我有一个表:
id flag
1 Y
1 Y
1 Y
1 N
1 N
2 Y
2 N
2 N
3 Y
3 N
我想做选择的语句,这将为我提供以下输出。
id count_flag_Y count_flag_N
1 3 2
2 1 2
3 1 1
我正在尝试使用 select case
方法,但要获得语法错误。
SELECT id,SUM(CASE WHEN flag= 'Y') as count_flag_Y,
SUM(CASE WHEN flag= 'N') as count_flag_N
from tablename
GROUP BY id
有什么方法可以做?
您的查询不远,您只有CASE
表达式有一个小问题。尝试以下操作:
SELECT id,
SUM(CASE WHEN flag= 'Y' THEN 1 ELSE 0 END) AS count_flag_Y,
SUM(CASE WHEN flag= 'N' THEN 1 ELSE 0 END) AS count_flag_N
FROM tablename
GROUP BY id
您非常接近,但是在mysql中不需要CASE
:
SELECT id, SUM(flag = 'Y') as count_flag_Y,
SUM(flag = 'N') as count_flag_N
FROM tablename
GROUP BY id;
mysql在数字上下文中像整数一样对待布尔语表达,for true和forse的" 1"。