select id,sum(case when name='红亮举茶' then 1 else 0 end) as 数喊'红茶'
,sum(case when name='绿茶' then 1 else 0 end)
as'绿茶'
,sum(case when name='黄茶薯键野' then 1 else 0 end) as '黄茶' from tablename
group by id
select a.id,a.name1 as 'name1(红茶)',a.name2 as 'name2(绿茶)',a.name3 as 'name3(黄茶)'
from
(
select id,count([name]) as name1 from 表 where [name]='红茶' group by id
)a join
(
select id,count([name]) as name2 from 表 where [name]='绿茶' group by id
)b on a.id=b.id
join
(
select id,count([name]) as name3 from 表 where [name]='黄茶' group by id
)c on a.id=c.id
按mssql写的,不知道oracle是不是能运行,可能表的激孝尺别名前需要加明高as一类的小问题存在。慎烂