一个表是这样的。
name level ...
---------------
tom A
tom B
tom A
jack B
jack C
jack C
我想用sql 语句得到这样的结果
name A B C
-------------------
tom 2 1 0
jack 0 1 2 也就是统计出每个人的A有多少个。B有多少个。只有A,B,C不会有D或其它
数据库服务器是sql server ,请问如何写sql
name level ...
---------------
tom A
tom B
tom A
jack B
jack C
jack C
我想用sql 语句得到这样的结果
name A B C
-------------------
tom 2 1 0
jack 0 1 2 也就是统计出每个人的A有多少个。B有多少个。只有A,B,C不会有D或其它
数据库服务器是sql server ,请问如何写sql
name,
sum(case level when A then 1 else 0 end) as A,
sum(case level when B then 1 else 0 end) as B,
sum(case level when C then 1 else 0 end) as C
from tb
group by name
name,
sum(case level when 'A' then 1 else 0 end) as A,
sum(case level when 'B' then 1 else 0 end) as B,
sum(case level when 'C' then 1 else 0 end) as C
from tb
group by name
name,
sum(case level when 'A' then 1 else 0 end) as A,
sum(case level when 'B' then 1 else 0 end) as B,
sum(case level when 'C' then 1 else 0 end) as C
from tablename
group by name
order by ....
drop table tab
gocreate table tab(
name varchar(10),
level char(1)
)
insert into tab(name,level) values('tom','A')
insert into tab(name,level) values('tom','B')
insert into tab(name,level) values('tom','A')
insert into tab(name,level) values('jack','B')
insert into tab(name,level) values('jack','C')
insert into tab(name,level) values('jack','C')select
name,
sum(case level when 'A' then 1 else 0 end) as A,
sum(case level when 'B' then 1 else 0 end) as B,
sum(case level when 'C' then 1 else 0 end) as C
from tab
group by namedrop table tabname A B C
---------- ----------- ----------- -----------
jack 0 1 2
tom 2 1 0(所影响的行数为 2 行)
(select TableName from CategoryName where Japanese='ライト・照明') t
insert roy_ta
select 'tom', 'A' union all
select 'tom', 'B' union all
select 'tom', 'A' union all
select 'jack', 'B' union all
select 'jack', 'C' union all
select 'jack', 'C'
--动态查询
declare @sql varchar(4000)
set @sql=''
select @sql=@sql+',['+[level]+']=sum(case level when '''+[level]+''' then 1 else 0 end)'
from roy_ta group by [level]
set @sql='select [name] '+@sql+' from roy_ta group by name order by name desc'
exec(@sql)
--静态查询
select [name],[A]=sum(case level when 'A' then 1 else 0 end),
[B]=sum(case level when 'B' then 1 else 0 end),
[C]=sum(case level when 'C' then 1 else 0 end)
from roy_ta group by [name] order by [name]desc
name A B C
---------- ----------- ----------- -----------
tom 2 1 0
jack 0 1 2(所影响的行数为 2 行)--drop table roy_ta
http://community.csdn.net/Expert/topic/5128/5128216.xml?temp=.7214319
http://community.csdn.net/Expert/topic/5128/5128233.xml?temp=.140072
http://community.csdn.net/Expert/topic/5128/5128222.xml?temp=.5970423