假设我有这样的查询：
SELECT name,GROUP_CONCAT(number)
FROM objects
GROUP BY name
它输出：
+----------+----------------------+
| NAME | GROUP_CONCAT(NUMBER) |
+----------+----------------------+
| false_1 | 2,1 |
| false_2 | 3,4 |
| true_1 | 4,3,2,1 |
| true_2 | 2,3 |
+----------+----------------------+
现在我如何返回2和3作为数字的行？
注意：此查询已分组 – 表有10行,如下所示：
+---------+--------+
| NAME | NUMBER |
+---------+--------+
| true_1 | 1 |
| true_1 | 2 |
| true_1 | 3 |
| ... | ... |
+---------+--------+
最佳答案
SELECT name,GROUP_CONCAT(number)
FROM objects
WHERE number IN (2,3)
GROUP BY name
HAVING COUNT(*) = 2
或者如果要保留名称所具有的所有值,
SELECT a.name,GROUP_CONCAT(A.number)
FROM objects a
INNER JOIN
(
SELECT name
FROM objects
WHERE number IN (2,3)
GROUP BY name
HAVING COUNT(*) = 2
) b ON a.Name = b.Name
GROUP BY a.name