用于计数的mysql case语句

Question

表 t1

col1 col2 col3
---- ---- ----
  1   cat   2
  2   dog   3
  3   cat   4
  4   dog   5

表 t2

 id  type
---- ----
  1    a
  2    a
  3    b
  4    b

我想把它变成这样:

col2   a    b
---- ---- ----
 cat   2    4
 dog   3    5

查询

Select t1.col2, 
case when t2.type = 'a' then count(t1.col3) end as a,
case when t2.ab = 'b' then count(t1.col3) end as b 
From mytable t1 join mytable2 t2 on t1.col1=t2.id where t1.col3 is not null 
group by 1;

它只填充 a 或 b 列。

我的查询有什么问题？

Answer 1

您需要将 case 语句移动到 count()(或 sum())本身:

Select t1.col2, 
       count(case when t2.type = 'a' then 1 else null end) as a,
       count(case when t2.type = 'b' then 1 else null end) as b 
From mytable t1 join mytable2 t2 on t1.col1=t2.id where t1.col3 is not null 
group by 1;