sql - 返回所有子集中存在的 ID 的不同列表

Question

我有两张 table

Table "public.tags_to_entities"
 Column |  Type   | Modifiers 
--------+---------+-----------
 tid    | integer | not null
 eid    | integer | not null

   Table "public.tag_tree"
 Column |  Type   | Modifiers 
--------+---------+-----------
 tid    | integer | not null
 pid    | integer | not null
 level  | integer | 

tag_tree 包含标签之间的所有关系，这意味着 SELECT pid FROM tag_tree WHERE tid = ? 将返回该标签的父级。 level 列存在或仅 ORDER BY。

我想返回每个标签子集中至少有一个标签的所有 eid 的列表。使用以下查询执行一个子集

SELECT DISTINCT eid
FROM tags_to_entities
WHERE
    tags_to_entities.tid = 1 OR
    tags_to_entities.tid IN (SELECT tag_tree.tid FROM tag_tree WHERE tag_tree.pid = 1));

这将返回标记 1 或其子标记之一中存在的所有 eid。如果我想返回至少一个与 1 和 2 相关的标签中存在的所有 eid 。到目前为止我失败的方法是

SELECT DISTINCT eid
FROM tags_to_entities
WHERE
    (
        tags_to_entities.tid = 1 OR
        tags_to_entities.tid IN (SELECT tag_tree.tid FROM tag_tree WHERE tag_tree.pid = 1)) AND
    (
        tags_to_entities.tid = 2 OR
        tags_to_entities.tid IN (SELECT tag_tree.tid FROM tag_tree WHERE tag_tree.pid = 2));

这不起作用，因为 tid 不能同时为 1 和 2。我该如何解决这个问题？ (eid稍后会与entrys表连接)

Answer 1

您需要使用GROUP BY而不是DISTINCT 。这允许您使用聚合函数和 HAVING条款。

SELECT
  map.eid
FROM
  tags_to_entities    AS map
INNER JOIN
  tag_tree            AS tree
    ON map.tid = tree.tid
WHERE
     (tree.tid = 1 OR tree.pid = 1)
  OR (tree.tid = 2 OR tree.pid = 2)
GROUP BY
  map.id
HAVING
  COUNT(DISTINCT CASE WHEN (tree.tid = 1 OR tree.pid = 1) THEN 1
                      WHEN (tree.tid = 2 OR tree.pid = 2) THEN 2
                 END)
  = 2

JOIN和WHERE子句获取具有标签 1 的所有实体或 2 。但是然后你将它们分组在一起，然后计算这些标签属于多少个不同的类别。只有当它是 2 时，该实体才能通过HAVING。条款。