logo标签列表

SQL查询以过滤相关行组。

sqldjangolinq
3

我在 SQL 查询中遇到了一个反复出现的问题，我无法通过原始 SQL 或 Django ORM 优雅地解决它，现在在 EntityFramework 中也面临同样的问题。这可能是一个常见的问题，有自己的名字，但我不知道它的名字。

假设我有两个表之间的简单外键关系，例如：

Book 1 <- * Tag

一本书有多个标签，每个标签只属于一本书，即Tag表有一个外键指向Book表。

现在，我想找到所有含有"Tag1"和"Tag2"的书。

原始SQL语句

我可以进行多次连接操作。

SELECT * FROM books
JOIN tags t1 on tags.book_id = books.id
JOIN tags t2 on tags.book_id = books.id
WHERE t1.tag = 'Tag1' AND t2.tag = 'Tag2'

很好，这可以实现，但看起来不太性能优越。

Django

在Django中，我可以进行类似的操作。

Book.objects.filter(tags__tag="Tag1").filter(tags__tag="Tag1")

EntityFramework LINQ

如果像这样更改过滤器，将会导致额外的连接，类似于原始的SQL版本中的情况。

我尝试链接 .Where() 类似于更改Django的 .filter()，但是它没有相同的结果。 它将构建一个类似以下的查询，这当然会返回空，因为没有行的标签是两个不同的字符串。

SELECT * FROM books
JOIN tags t1 on tags.book_id = books.id
WHERE t1.tag = 'Tag1' AND t1.tag = 'Tag2'

总结

我想我可以使用数组聚合来将标签聚合成一个数组，然后进行比较，但是这似乎也很耗费资源。而且，聚合和分组也会影响到事物的排序，这就迫使我要使用子查询来得到我想要的顺序。

我绝对不是SQL方面的专家，正如你所看到的那样，但我希望能做到以下两点：

  1. 通过LINQ模仿上面愚蠢无意义的操作
  2. 找到一种更加优雅的方法，让我能够实现自己的需求，并与任何ORM兼容

额外的废话

当我需要查找拥有“所有”列表中标签的书籍时，这才是棘手的部分...如果是“任何一个”或“特定的一个”，那就简单了。

编辑：使用数组和重叠解决方案

在Postgres中，我们可以使用 array_agg 将所有相关标签聚合成一个数组，像这样：

SELECT * FROM books
JOIN tags t1 on tags.book_id = books.id
;

+--------+-------+------+
| BookId | Name  | Tag  |
+--------+-------+------+
|      1 | BookA | Tag1 |
|      1 | BookA | Tag2 |
|      1 | BookA | Tag3 |
|      2 | BookB | Tag1 |
|      2 | BookB | Tag3 |
+--------+-------+------+


SELECT books.BookId, Name, array_agg(t1.tags) as tags
FROM books
JOIN tags t1 on tags.book_id = books.id
GROUP BY BookId
ORDER BY BookId
;

+--------+-------+--------------------+
| BookId | Name  |        tags        |
+--------+-------+--------------------+
|      1 | BookA | [Tag1, Tag2, Tag3} |
|      2 | BookB | {Tag1, Tag3}       |
+--------+-------+--------------------+

有了这样，我就可以使用数组“包含”运算符来查找标记（tag）与预期集合重叠的行：WHERE tags @> ('Tag1', 'Tag2')。

这也是一个可行的选项。它进行聚合而不是过度连接。但不确定使用 LINQ 查询会看起来像什么。

tag_list=['demo', 'test'] 也许是这个：Book.objects.filter(tags__tag__in=tag_list)。 - Hemal Patel
@IvanStarostin 谢谢。这个建议是在你的评论几个小时后的一个答案中提出的。虽然这对于这种情况可以起作用，但它不是通用解决方案的最佳方法，因为它可能会以各种方式破坏...（请参见下面相关答案的评论） - Eldamir
你说一本书有很多标签，一个标签只属于一本书，但在额外的闲扯中，标签1既出现在书A上又出现在书B上。你能解释一下为什么吗？ - Orfeas Bourchas
如果您能告诉我您主要想针对哪个平台，那将会很有帮助。如果涉及到 SQL、Django 和 EF 三个平台，那么这个问题就太宽泛了，因为答案可能只包含解决方案的一部分。 - Gert Arnold
请将您的问题简洁地询问SQL相关内容。不要冗长或者无关的描述。如果没有赏金，这个问题可能会被关闭，因为它需要更多的焦点。您不能使用赏金来（暂时）防止问题被关闭。我现在添加了一个答案，显然您主要不是在寻找这个答案。 - Gert Arnold
显示剩余6条评论
4个回答
3

使用 group by 和 having，我们可以进行 标签1 和 标签2 的操作。

with cte_tags as (
select book_id
  from tags
 where tag in ('Tag 1', 'Tag 2')
 group by book_id
 having count(*)=2)
select b.id as book_id,
       b.name
  from books b
  join cte_tags t
    on b.id = t.book_id;

编辑：

  1. 如果 book_id 的标签 tags 可以重复，则使用以下内容：
with cte_tags as (
select book_id
  from tags
 where tag in ('Tag 1', 'Tag 2')
 group by book_id
 having count(distinct tag)=2)
select b.id as book_id,
       b.name
  from books b
  join cte_tags t
    on b.id = t.book_id;

如果需要查找tag1或tag2，或者tag1和tag2，请使用以下方法：
with cte_tags as (
select book_id
  from tags
 where tag in ('Tag 1', 'Tag 2')
 group by book_id
 having count(distinct tag) between 1 and 2)
select b.id as book_id,
       b.name
  from books b
  join cte_tags t
    on b.id = t.book_id;
查询具有“任一”标签的书籍（不是两者都有），但随后进行计数以确保您只找到同时具有两个标签的书籍... 这样做可以解决问题，但也不太优雅。此外，在其他情况下使用时，我需要注意不能有重复的链接。例如，有两个指向“标签1”的链接，或将其与其他连接组合起来，这些连接会添加重复的行条目，因此count(*)=2将是不正确的... 这不是一个坏的解决方案... 谢谢您的参与 :) - Eldamir
如果标签可以重复，使用count(distinct tag)=2。 - JHH
1
如果我理解正确，您想要只有标签'Tag1'和'Tag2'的图书。即没有其他标签。我不知道这个问题的官方名称，也许是“独占包含”。
它等同于找到符合两个条件的图书：
  • 所有标签都为("Tag1", "Tag2")
  • 具有两个唯一标签(或没有其他或零个标签)
您正在寻找可与任何ORM一起使用的解决方案。当然，这是不可能的。但是，有一个LINQ解决方案，可能适用于任何基于LINQ的ORM。无论如何，它适用于EF。
当标签名称对每本书不唯一时，可以使用此查询：
var tags = new[] { "Tag1", "Tag2" };
var books = context.Books
    .Where(b => b.Tags.All(t => tags.Contains(t.Tag))
        && b.Tags.Select(t => t.Tag).Distinct().Count() == tags.Count());

如果每本书的标签名称是唯一的（我假设是这样的），则可以这样做：
var books = context.Books
    .Where(b => b.Tags.All(t => tags.Contains(t.Tag))
        && b.Tags.Count() > 0);

第二个条件是必要的，否则没有任何标签的书籍也会被选中（这就是 All 的语义）。
这将生成 SQL 查询，您需要与之解决（就像使用任何基于 LINQ 的 ORM 一样）。对于第二种情况，使用 EF-core 6：
  SELECT [b].[Id]
  FROM [Books] AS [b]
  WHERE NOT EXISTS (
      SELECT 1
      FROM [Tags] AS [t]
      WHERE ([b].[Id] = [t].[BookId]) AND [t].[Tag] NOT IN (N'Tag1', N'Tag2'))
      AND ((
          SELECT COUNT(*)
          FROM [Tags] AS [t0]
          WHERE [b].[Id] = [t0].[BookId]) > 0)

当然，手动编写SQL查询语句在特定的索引和统计信息组合下可以（远远）更好地发挥性能。此外，大多数（如果不是全部）ORM都提供了执行原始SQL的方法。从这个意义上讲，“任何”ORM都可以使用SQL查询。但我不认为这是“ORM解决方案”。它并没有使用ORM的核心机制，只是使用了它的数据库连接。

1
如果性能很重要，您应该使用实际数据在服务器上尝试各种查询并测量它们的性能。
我有个一般性的注意事项。
像这样的查询：
select book_id
from tags
where tag in ('Tag1', 'Tag2')

或者像这样：

select book_id
from tags
where tag = 'Tag 1' OR tag = 'Tag2'

通常情况下，即使在tag列上有索引，扫描整个表格tags的查询也会被执行。

另一方面，像这样的查询：

select book_id
from tags
where tag = 'Tag1'

通常会使用索引。

因此，我们可以将带有OR的查询扩展为两个单独的查询，然后组合它们的结果。

WITH
CTE_BookIDs
AS
(
    select book_id
    from tags
    where tag = 'Tag1'

    INTERSECT

    select book_id
    from tags
    where tag = 'Tag2'
)
SELECT
    books.*
FROM
    books
    INNER JOIN CTE_BookIDs ON CTE_BookIDs.book_id = books.id
;

这是一个针对样本数据集的查询：

CREATE TABLE #Tags
    (ID int IDENTITY NOT NULL PRIMARY KEY
    ,BookID int NOT NULL
    ,Tag varchar(50) NOT NULL);

INSERT INTO #Tags VALUES
(1, 'Tag1'),
(1, 'Tag2'),
(1, 'Tag3'),
(1, 'Tag4'),
(2, 'Tag1'),
(3, 'Tag2'),
(4, 'Tag1'),
(4, 'Tag2'),
(4, 'Tag3'),
(5, 'Tag3'),
(5, 'Tag4'),
(5, 'Tag5'),
(6, 'Tag1'),
(6, 'Tag3'),
(6, 'Tag5'),
(7, 'Tag2'),
(7, 'Tag3'),
(8, 'Tag1'),
(8, 'Tag2');

CREATE INDEX IX_Tag ON #Tags
(
    Tag, BookID
);

WITH
CTE_BookIDs
AS
(
    select BookID
    from #Tags
    where tag = 'Tag1'

    INTERSECT

    select BookID
    from #Tags
    where tag = 'Tag2'
)
SELECT *
FROM CTE_BookIDs
;

DROP TABLE #Tags;

结果

+--------+
| BookID |
+--------+
|      1 |
|      4 |
|      8 |
+--------+

执行计划

execution plan


（注：此处无需翻译，为图片）
1
没有想到可以这样使用交集.. 这将消除对 "count" 进行过滤的需求，我非常感激... 我觉得计数可能是一种维护负担。稍后编辑时很容易出现错误.. 交集的想法是可靠的。 - Eldamir
这种方法的缺点是很难推广到变量数量的标签。 - Gert Arnold
@GertArnold，你是对的。此外，在标签数量超过一定阈值后，扫描整个“tags”表一次会比多次索引查找和合并结果更快。 - Vladimir Baranov
0
尝试下一个解决方案：
首先创建索引以加速此查询。
create index on tags (tag, book_id);

检查下一个查询
SELECT * FROM books
JOIN tags t1 on t1.tag = 'Tag1' AND t2.book_id = books.id
JOIN tags t2 on t2.tag = 'Tag2' AND t2.book_id = books.id;

如果您想处理任意数量标签的任意列表：

SELECT 
   books.id,
   count(distinct tags.id) as tags_count
FROM books
JOIN tags on tags.tag = ANY(['Tag1', 'Tag2', ...]) AND tags.book_id = books.id
GROUP BY books.id
HAVING 
   count(distinct tags.id) = <number of tags>

为了加速第二个查询，请检查下一个索引是否有帮助

create index on tags (book_id, tag);
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接