带参数的 MySQL 计数 [英] MySQL count with parameters

问题描述

我有这样的 MySQL 表

i have MySQL table like this

ID  |user_id    |type   |extra
---------------------------------------------
1   |1          |1      |0
2   |2          |1      |0
3   |2          |1      |2
4   |1          |1      |2
5   |1          |2      |0
6   |1          |1      |2
7   |2          |2      |2
8   |3          |1      |0
9   |2          |2      |2
10  |2          |2      |0

我需要按 user_id 计算它以获得如下输出:

I need to count it by user_id to get output like:

user_id |one_noextra    |one_twoextra   |two_noextra    |two_twoextra
---------------------------------------------------------------------
1       |1              |2              |1              |0
2       |1              |1              |1              |2
3       |1              |0              |0              |0

(我不知道，如果我算对了，但我希望你明白.)

(I don't know, if I counted it right, but I hope you get the point.)

有什么办法可以在 MySQL 中实现这一点吗?

Is there any way to achieve this in MySQL?

感谢您的帮助.

推荐答案

如果 type,extra 受到限制，请查看您的示例数据集，即 type 只能有 (1,2) 而 extra 只能有 (0,2) 您可以这样做，使用总和表达式将根据表达式的结果得出 0 或 1，您可以相应地进行计数

Viewing your sample dataset if type,extra are limited i.e type can only have (1,2) and extra can only have (0,2) you can do so,using expression in sum it will result as 0 or 1 based on the outcome of the expression and you can have your count accordingly

SELECT 
user_id,
SUM(`type`=1 AND  `extra`=0) one_noextra,
SUM(`type`=1 AND  `extra`=2) one_twoextra,
SUM(`type`=2 AND  `extra`=0) two_noextra,
SUM(`type`=2 AND  `extra`=2) two_twoextra
FROM Table1
GROUP BY user_id

小提琴演示

这篇关于带参数的 MySQL 计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！