MySQL 对特定值的列进行计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15815011/
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 count columns on specific value
提问by Christy Herron
I have the following db table, and I would like to be able to count the instance of sales of certain products per salesperson.
我有以下 db 表，我希望能够计算每个销售人员的某些产品的销售实例。
|------------|------------|------------|
|id |user_id |product_id |
|------------|------------|------------|
|1 |1 |2 |
|2 |1 |4 |
|3 |1 |2 |
|4 |2 |1 |
|------------|------------|------------|
I would like to able to create a result set like the following;
我希望能够创建如下所示的结果集；
|------------|-------------|------------|------------|------------|
|user_id |prod_1_count |prod_2_count|prod_3_count|prod_4_count|
|------------|-------------|------------|------------|------------|
|1 |0 |2 |0 |1 |
|2 |1 |0 |0 |0 |
|------------|-------------|------------|------------|------------|
I am creating graphs with this data, and once again (as earlier today) I am unable to count the column totals. I have tried;
我正在使用这些数据创建图表，并且再次（如今天早些时候）我无法计算列总数。我试过了;
SELECT user_id,
(SELECT count(product_id) FROM sales WHERE product_id = 1) AS prod_1_count,
(SELECT count(product_id) FROM sales WHERE product_id = 2) AS prod_2_count,
(SELECT count(product_id) FROM sales WHERE product_id = 3) AS prod_3_count,
(SELECT count(product_id) FROM sales WHERE product_id = 4) AS prod_4_count
FROM sales GROUP BY user_id;
I can see why this doesn't work, because for each bracketed SELECT the user_id doesn't match the external user_id in the main SELECT statement.
我可以理解为什么这不起作用，因为对于每个带括号的 SELECT，user_id 与主 SELECT 语句中的外部 user_id 不匹配。
Can anyone help out me here?
有人可以在这里帮助我吗？
thanks you
感谢您
回答by Ike Walker
You can do this using SUMand CASE:
select user_id,
sum(case when product_id = 1 then 1 else 0 end) as prod_1_count,
sum(case when product_id = 2 then 1 else 0 end) as prod_2_count,
sum(case when product_id = 3 then 1 else 0 end) as prod_3_count,
sum(case when product_id = 4 then 1 else 0 end) as prod_4_count
from your_table
group by user_id
回答by Taryn
You are trying to pivotthe data. MySQL does not have a pivot function so you will have to use an aggregate function with a CASEexpression:
您正在尝试旋转数据。MySQL 没有枢轴函数，因此您必须使用带有CASE表达式的聚合函数：
select user_id,
count(case when product_id = 1 then product_id end) as prod_1_count,
count(case when product_id = 2 then product_id end) as prod_2_count,
count(case when product_id = 3 then product_id end) as prod_3_count,
count(case when product_id = 4 then product_id end) as prod_4_count
from sales
group by user_id;
回答by d'alar'cop
See if this works:
看看这是否有效：
SELECT a.user_id,
(SELECT count(b.product_id) FROM sales b WHERE b.product_id = 1 AND a.user_id = b.user_id) AS prod_1_count,
(SELECT count(b.product_id) FROM sales b WHERE b.product_id = 2 AND a.user_id = b.user_id) AS prod_2_count,
(SELECT count(b.product_id) FROM sales b WHERE b.product_id = 3 AND a.user_id = b.user_id) AS prod_3_count,
(SELECT count(b.product_id) FROM sales b WHERE b.product_id = 4 AND a.user_id = b.user_id) AS prod_4_count
FROM sales a GROUP BY a.user_id;
Cheers. n.b. there may be slightly nicer ways to achieve the equivalent result.
干杯。nb 可能有更好的方法来实现等效的结果。
