MySQL GROUP BY“和过滤器”

假设我有这样的查询：

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      |
| ...     | ...    |
+---------+--------+

[Link to SQLFiddle] 最佳答案

SELECT name,GROUP_CONCAT(number)
FROM objects
WHERE number IN (2,3)
GROUP BY name
HAVING COUNT(*) = 2

> SEE SQLFiddle Demo

或者如果要保留名称所具有的所有值,

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

> SEE SQLFiddle Demo

（编辑：4S站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!