group by 与count 的问题 ,求一SQL
create table tb(id int ,class varchar)--class种类就只有三种
insert tb
select 1, 'a ' union all
select 1, 'a ' union all
select 1, 'b ' union all
select 1, 'c ' union all
select 2, 'a ' union all
select 2, 'b ' union all
select 2, 'b '
select * from tb
想查找出按id分组得到的 a ,b ,c 的数量
如下
id a b c
1 2 1 1
2 1 2 0
求一SQL
[解决办法]
select
id,
a=sum(case class when 'a ' then 1 else 0 end),
b=sum(case class when 'b ' then 1 else 0 end),
c=sum(case class when 'c ' then 1 else 0 end)
from
tb
group by
id
[解决办法]
select id,
sum(case when class= 'a ' then 1 else 0 end) a,
sum(case when class= 'b ' then 1 else 0 end) b,
sum(case when class= 'c ' then 1 else 0 end) c
from tb
group by id
order by id
[解决办法]
select id,class,count(*) from tb group by class,id
