MySQL - 查询 - 多个分组依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5021243/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - query - multiple group by
提问by Josh
I have the following table, in which I'm trying to SUM if type = 'printer', however, I would like not to count repeat client_ids. So I expect something like this:
我有下表，如果 type = 'printer'，我正在尝试求和，但是，我不想计算重复的 client_ids。所以我期待这样的事情：
+------+-----------+-----------+
| k_id | client_id | type |
+------+-----------+-----------+
| 1 | 100 | pc |
| 2 | 101 | printer |
| 3 | 101 | printer |
| 4 | 101 | printer |
| 5 | 102 | cellphone |
+------+-----------+-----------+
Query:
询问：
SELECT client_id,
SUM(IF(type = 'printer', 1,0))
FROM FOO
GROUP BY type, client_id;
Result:
结果：
+-----------+--------------------------------+
| client_id | SUM(IF(type = 'printer', 1,0)) |
+-----------+--------------------------------+
| 102 | 0 |
| 100 | 0 |
| 101 | 3 |
+-----------+--------------------------------+
Expected result:
预期结果：
+-----------+--------------------------------+
| client_id | SUM(IF(type = 'printer', 1,0)) |
+-----------+--------------------------------+
| 102 | 0 |
| 100 | 0 |
| 101 | 1 |
+-----------+--------------------------------+
采纳答案by Andomar
There are three rows with a type of printer. Sumadds them all up, and returns 3.
有 3 行，类型为printer。 Sum将它们全部相加，并返回 3。
If you'd like to see 1for rows with printers, and 0otherwise, try maxinstead of sum:
如果您想查看1带有打印机的行，0否则请尝试max代替sum：
MAX(IF(type = 'printer', 1,0))
^^^
EDIT: To count the number of distinct printers, you could use a subquery:
编辑：要计算不同打印机的数量，您可以使用子查询：
SELECT client_id
, (
select count(*)
from FOO as f2
where f1.client_id = f2.client_id
and type = 'Printer'
)
FROM FOO as f1
GROUP BY
client_id
回答by OMG Ponies
Use:
用：
SELECT x.client_id,
COUNT(DISTINCT y.type)
FROM FOO x
LEFT JOIN FOO y ON y.client_id = x.client_id
AND y.type = 'printer'
GROUP BY x.client
If you don't need to see the rows with zero counts:
如果您不需要查看计数为零的行：
SELECT client_id,
COUNT(DISTINCT type)
FROM FOO
WHERE type = 'printer'
GROUP BY type, client_id;
回答by Arumugam
SELECT client_id, if( `type` = 'printer', 1, 0 )
FROM foo
GROUP BY TYPE , client_id
回答by Aaron Watters
SELECT distinct client_id,
(IF(type = 'printer', 1,0))
FROM FOO
(I'm guessing: I'm not aquainted with IF(..))
（我猜：我不熟悉 IF(..)）
