MySQL 查询在同一个表上有 2 个查询以匹配完全相同的值

Question

我有一个包含 2 个字段的表，名称和编号。我需要获取包含列表中完全匹配的所有数字的名称。例如。

表详细信息

----------
name | number
----------
A    |   1    
A    |   2    
B    |   1    
C    |   1
D    |   1    
D    |   2   
D    |   3

如果我的列表是 (1,2) ，输出应该只有 A如果我的列表是 (1,2,3) ，输出应该只有 D如果我的列表是 (1) ，输出应该是 B,C

这是我尝试过的

SELECT name FROM details 
WHERE number IN (1,2) 
GROUP BY name 
HAVING COUNT(DISTINCT number)=2

Answer 1

一些值得思考的事情:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(name CHAR(1) NOT NULL
,number INT NOT NULL
,PRIMARY KEY(name,number)
);

INSERT INTO my_table VALUES
('A',1),
('A',2),
('B',1),
('C',1),
('D',1),
('D',2),
('D',3);

SELECT *, number IN (1,2) x FROM my_table;
+------+--------+---+
| name | number | x |
+------+--------+---+
| A    |      1 | 1 |
| A    |      2 | 1 |
| B    |      1 | 1 |
| C    |      1 | 1 |
| D    |      1 | 1 |
| D    |      2 | 1 |
| D    |      3 | 0 |
+------+--------+---+

SELECT name
     , COUNT(*)
     , SUM(number IN (1,2)) x 
  FROM my_table 
 GROUP 
    BY name;
+------+----------+------+
| name | COUNT(*) | x    |
+------+----------+------+
| A    |        2 |    2 |
| B    |        1 |    1 |
| C    |        1 |    1 |
| D    |        3 |    2 |
+------+----------+------+

...以防万一它需要拼写...

SELECT name
  FROM my_table
 GROUP
    BY name
HAVING COUNT(*) = 2 -- DISTINCT is redundant because of my PRIMARY KEY
   AND COUNT(*) = SUM(number IN (1,2));
;
+------+
| name |
+------+
| A    |
+------+