交叉引用时匹配SQL中的集

Question

我有以下表格：

• 书籍：书籍列表
• 标签：标签列表（小说、传记等）
• xrefbookstags:交叉引用表（一本书有多个标记）

我想做的是给一组标签，找到标签与该集完全匹配的书籍。以下是我尝试过的：

SELECT B.name, B.author, B.id, B.finished, B.manual
    FROM books B INNER JOIN xrefbookstags XRBT 
        ON XRBT.idBooks=B.id JOIN tags T
        ON XRBT.idTags=T.id
    WHERE T.name IN ('novel','biography')

这给了我一个包含，而不是集合的相等。所以我想我应该计算集合中的标签数量，并匹配集合的大小。

SELECT B.name, B.author, B.id, B.finished, B.manual
    FROM books B INNER JOIN xrefbookstags XRBT 
        ON XRBT.idBooks=B.id JOIN tags T
        ON XRBT.idTags=T.id
    WHERE T.name IN ('novel','biography') AND count(T.id)=2

但这失败了。所以我的问题来了：我可以在这个交叉引用的上下文中匹配集合吗？

Answer 1

您想要

a） 该书没有缺少必需的标签，ANDb） 该书没有不在必需标签列表中的标签。

将这些标签名称的id放入一个表中（这里是动态完成的），然后执行此操作。（没有SQL Fiddle或CREATE TABLE/INSERT示例数据，所以我没有运行它来检查每个细节）

WITH TagIDs(id) AS ( --required tag ids
  SELECT id
  FROM tags
  WHERE name in ('novel', 'biography')
)
  SELECT B.name, B.author, B.id, B.finished, B.manual --select those books
  FROM books B
  WHERE --a)
  NOT EXISTS (  -- there is no
    SELECT * FROM TagIDs --  required tag
    WHERE TagIDs.id NOT IN ( -- that's missing
      SELECT XRBT.idTags FROM xrefbookstags XRBT -- from the list of tags
      WHERE XRBT.idBooks=B.id -- for this particular book
    )
  )
  AND --b)
  NOT EXISTS ( -- there is no
    SELECT * FROM xrefbookstags XRBT --tag
    WHERE XRBT.idBooks=B.id --for this particular book
    AND XRBT.idTags NOT IN ( --that's missing
      SELECT id FROM TagIDs --from the list of required tags
    )
  )

Answer 2

您可以组合EXISTS和NOT EXISTS:

SELECT B.name, B.author, B.id, B.finished, B.manual
FROM books B
WHERE EXISTS(
    SELECT 1 FROM xrefbookstags XRBT 
    INNER JOIN Tags T ON XRBT.idTags=T.id
    WHERE XRBT.idBooks=B.id
    AND T.name = 'novel'
)
AND EXISTS(
    SELECT 1 FROM xrefbookstags XRBT 
    INNER JOIN Tags T ON XRBT.idTags=T.id
    WHERE XRBT.idBooks=B.id
    AND T.name = 'biography'
) 
AND NOT EXISTS(
    SELECT 1 FROM xrefbookstags XRBT 
    INNER JOIN Tags T ON XRBT.idTags=T.id
    WHERE XRBT.idBooks=B.id
    AND T.name NOT IN ('novel','biography')
)

Answer 3

这是一个"集中的集合"查询的例子。最通用的方法是使用group by并将逻辑放入having子句中。对于您的案例：

SELECT B.name, B.author, B.id, B.finished, B.manual
FROM books B INNER JOIN
     xrefbookstags XRBT 
     ON XRBT.idBooks = B.id JOIN
     tags T
     ON XRBT.idTags = T.id
group by B.name, B.author, B.id, B.finished, B.manual
having sum(case when t.name = 'novel' then 1 else 0 end) > 0 and
       sum(case when t.name = 'biography' then 1 else 0 end) > 0 and
       sum(case when t.name not in ('novel', 'biography') then 1 else 0 end) = 0;

其逻辑如下。当至少有一个标记为'novel'时，第一个子句为true。当至少一个子句为'biography'时，第二个子句为true，而当不存在其他标记时，第三个子句为false。

这很容易概括。如果你想要有这两个标签但也可以有其他标签的书，只需省略第三条：

having sum(case when t.name = 'novel' then 1 else 0 end) > 0 and
       sum(case when t.name = 'biography' then 1 else 0 end) > 0;

如果你想要有一本或另一本的书：

having sum(case when t.name = 'novel' then 1 else 0 end) > 0 or
       sum(case when t.name = 'biography' then 1 else 0 end) > 0;

如果你想要有这两个加上"历史"的书，那么你只需要添加它：

having sum(case when t.name = 'novel' then 1 else 0 end) > 0 and
       sum(case when t.name = 'biography' then 1 else 0 end) > 0 and
       sum(case when t.name = 'historical' then 1 else 0 end) > 0;

而且，如果你想要，但不想做饭：

having sum(case when t.name = 'novel' then 1 else 0 end) > 0 and
       sum(case when t.name = 'biography' then 1 else 0 end) > 0 and
       sum(case when t.name = 'historical' then 1 else 0 end) > 0 and
       sum(case when t.name = 'cooking' then 1 else 0 end) = 0;

编辑：

如果你有一个逗号分隔的标签列表，你想匹配，那么你可以做：

having sum(case when ','+@List+',' not like '%,'+t.name+',%' then 1 else 0 end) = 0 and
       count(distinct t.name) = 1 + len(@list) - len(replace(@list, ',', ''))

第一个子句表示所有标签都在列表中。第二种说法是，标签的长度就是列表的长度。

这本质上是伪代码。不同的数据库对len()函数有不同的名称、将字符串连接在一起的不同方式以及表达变量值的不同方式。但其意图应该是明确的。