查找具有相同 ID 且具有一组特定名称的行

Question

编辑：

我有一个像这样有 3 行的表。

ID    NAME REV
1     A    0
1     B    0
1     C    0
2     A    1
2     B    0
2     C    0
3     A    1
3     B    1

我想找到具有一组特定名称且 REV 相同的 ID 例子： Edit2：GBN 的解决方案本来可以完美地工作，但因为我无权创建新表。添加的约束是不能创建新表。

if input = A,B then output is 3
if input = A ,B,C then output is 1 and not 1,2 since the rev level differs in 2.

Answer 1

最简单的方法是将每个 ID 的 COUNT 与列表中的元素数量进行比较：

SELECT
   ID
FROM
   MyTable
WHERE
   NAME IN ('A', 'B', 'C')
GROUP BY
   ID
HAVING
   COUNT(*) = 3;

注意：不需要 ORDER BY，如果需要，则在 HAVING 之后进行

编辑，并更新问题。在MySQL中，使用单独的表来存储搜索词会更容易

DROP TABLE IF EXISTS gbn;
CREATE TABLE gbn (ID INT, `name` VARCHAR(100), REV INT);
INSERT gbn VALUES (1, 'A', 0);
INSERT gbn VALUES (1, 'B', 0);
INSERT gbn VALUES (1, 'C', 0);
INSERT gbn VALUES (2, 'A', 1);
INSERT gbn VALUES (2, 'B', 0);
INSERT gbn VALUES (2, 'C', 0);
INSERT gbn VALUES (3, 'A', 0);
INSERT gbn VALUES (3, 'B', 0);

DROP TABLE IF EXISTS gbn1;
CREATE TABLE gbn1 ( `name` VARCHAR(100));
INSERT gbn1 VALUES ('A');
INSERT gbn1 VALUES ('B');

SELECT
   gbn.ID
FROM
   gbn
   LEFT JOIN
   gbn1 ON gbn.`name` = gbn1.`name`
GROUP BY
   gbn.ID
HAVING
   COUNT(*) = (SELECT COUNT(*) FROM gbn1)
   AND MIN(gbn.REV) = MAX(gbn.REV);

INSERT gbn1 VALUES ('C');

SELECT
   gbn.ID
FROM
   gbn
   LEFT JOIN
   gbn1 ON gbn.`name` = gbn1.`name`
GROUP BY
   gbn.ID
HAVING
   COUNT(*) = (SELECT COUNT(*) FROM gbn1)
   AND MIN(gbn.REV) = MAX(gbn.REV);

编辑2，无需额外的表，请使用派生（内联）表：

SELECT
   gbn.ID
FROM
   gbn
   LEFT JOIN
   (SELECT 'A' AS `name`
    UNION ALL SELECT 'B' 
    UNION ALL SELECT 'C'
   ) gbn1 ON gbn.`name` = gbn1.`name`
GROUP BY
   gbn.ID
HAVING
   COUNT(*) = 3 -- matches number of elements in gbn1 derived table
   AND MIN(gbn.REV) = MAX(gbn.REV);

Answer 2

与 gbn 类似，但允许重复 ID/名称组合的可能性：

SELECT ID
FROM MyTable
WHERE NAME IN ('A', 'B', 'C')
GROUP BY ID
HAVING COUNT(DISTINCT NAME) = 3;

Answer 3

好的！...我解决了我的问题！我修改了 GBN 的逻辑，使其无需使用 IN 子句

1 的搜索表即可完成 MAX(rev) = MIN(REV) 的缺陷：如果我有这样的数据。

ID    NAME   REV
1     A      0
1     B      1
1     A      1

然后，当我使用类似的查询时，

Select ID from TABLE
where NAME in {A,B}
groupby ID
having count(*) = 2
and MIN(REV) = MAX(REV)

它不会显示 ID 1，因为最小值和最大值不同，并且计数为 3。

因此，我只需向 groupby 添加另一列，

这样最终的查询就非常

Select ID from TABLE
where NAME in {A,B}
groupby ID,REV
having count(*) = 2
and MIN(REV) = MAX(REV)

感谢，感谢所有帮助。 ！