sql - 显示 GROUP BY 中的所有值

Question

如何通过语句获取组中的所有值？

mysql>select * from mytable;
+------+--------+--------+
| name | amount | status |
+------+--------+--------+
| abc  |     12 | A      | 
| abc  |     55 | A      | 
| xyz  |     12 | B      | 
| xyz  |     12 | C      | 
+------+--------+--------+
4 rows in set (0.00 sec)

mysql>select name, count(*) from mytable where status = 'A' group by name;
+------+----------+
| name | count(*) |
+------+----------+
| abc  |        2 | 
+------+----------+
1 row in set (0.01 sec)

预期结果:

+------+----------+
| name | count(*) |
+------+----------+
| abc  |        2 | 
| xyz  |        0 | 
+------+----------+

Answer 1

您可以使用一个有趣的技巧，其中 COUNT(column) 计算非空值的数量；您还使用了自连接(在执行此操作时):

SELECT a.name, COUNT(b.name)
  FROM mytable AS a LEFT OUTER JOIN mytable AS b
    ON a.name = b.name AND b.status = 'A'
 GROUP BY a.name;

这适用于所有版本的 SQL；并非所有变体都允许您对 bool 表达式求和，在支持时无疑更快、更直接。

另一种写法是:

SELECT a.name, COUNT(b.name)
  FROM mytable AS a LEFT OUTER JOIN
       (SELECT name FROM mytable WHERE status = 'A') AS b
    ON a.name = b.name
 GROUP BY a.name;