MySQL GROUP BY“和过滤器”

概述假设我有这样的查询：SELECT name, GROUP_CONCAT(number) FROM objects GROUP BY name 它输出：+----------+----------------------+ | NAME | GROUP_CONCAT(NUMBER) | +----------+---------------------

假设我有这样的查询：

SELECT name,GROUP_CONCAT(number)FROM objectsGROUP 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 objectsWHERE number IN (2,3)GROUP BY nameHAVING 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.nameGROUP BY a.name

> SEE SQLFiddle Demo 总结

以上是内存溢出为你收集整理的MySQL GROUP BY“和过滤器”全部内容，希望文章能够帮你解决MySQL GROUP BY“和过滤器”所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。