使用另一个表中的行过滤查询的输出

小编典典

使用另一个表中的行过滤查询的输出

sql

我需要一些代码的帮助

我有2个表，分别是“ stuff”和“ something”，并且我有此信息：

               stuff                                  something
+-------------+---------------------+    +-------------+---------------------+
| member_id   |     group_id        |    |  group_id   |      some_id        |
+-------------+---------------------+    +-------------+---------------------+
|      11     |         aa          |    |      aa     |         7           |
|      11     |         bb          |    |      dd     |         8           |
|      22     |         bb          |    |      bb     |         9           |
|      11     |         cc          |    +-------------+---------------------+
|      22     |         cc          |
|      33     |         cc          |
|      11     |         dd          |
+-------------+---------------------+

该查询：

SELECT  group_id
FROM    stuff a
WHERE   member_id IN (11)  -- <<== list of member_id (can be 11,22 or 22,33 or 11,22,33)
    AND
    EXISTS
    (
      SELECT group_id
      FROM stuff b
      WHERE a.group_id = b.group_id
      GROUP BY group_id
      HAVING COUNT(*) = 1                   -- <<== number of member_id
    )
GROUP   BY group_id
HAVING  COUNT(*) = 1                            -- <<== number of member_id

将返回aa和dd。

我的问题是：如果如何过滤结果some_id=7？

阅读 150

2021-05-16

共1个答案

小编典典

SELECT  `group_id`
FROM    `stuff` a
WHERE   `member_id` IN (11) -- <<== list of member_id (can be 11,22 or 22,33 or 11,22,33)
AND
EXISTS
(
    SELECT  `group_id`
    FROM    `stuff` b
    WHERE a.`group_id` = b.`group_id`
    AND
    EXISTS
    (
        SELECT  c.`group_id`
        FROM      `something` c
        WHERE   c.`group_id` = b.`group_id`
        AND     c.`some_id`=7                        -- <<== number of some_id=7
    )  
    GROUP BY `group_id`
    HAVING COUNT(*) = 1               -- <<== number of member_id
)
GROUP   BY `group_id`
HAVING  COUNT(*) =1               -- <<== number of member_id

这将返回aa…我想要的！

2021-05-16