求分组统计sql语句。
数据库表Score如下：（只有0分、10分、20分、30分四种分数）
姓名 上午得分 下午得分
张三 10 20
张三 0 10
李四 20 0
李四 0 0
李四 30 20
通过sql语句得到
姓名 0分 10分 20分 30分
张三 1 2 1 0
李四 3 0 2 1
求解 sql 数据库
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-03-31 01:00:04
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
--Jun 17 2011 00:57:23
--Copyright (c) Microsoft Corporation
--Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([姓名] varchar(4),[上午得分] int,[下午得分] int)
insert [huang]
select '张三',10,20 union all
select '张三',0,10 union all
select '李四',20,0 union all
select '李四',0,0 union all
select '李四',30,20
--------------开始查询--------------------------
select [姓名]
,[0分]=SUM(CASE WHEN [上午得分]=0 AND [下午得分]=0 THEN 2 WHEN [上午得分]=0 AND [下午得分]<>0 THEN 1
WHEN [上午得分]<>0 AND [下午得分]=0 THEN 1 ELSE 0 END )
,[10分]=SUM(CASE WHEN [上午得分]=10 AND [下午得分]=10 THEN 2 WHEN [上午得分]=10 AND [下午得分]<>10 THEN 1
WHEN [上午得分]<>10 AND [下午得分]=10 THEN 1 ELSE 0 END )
,[20分]=SUM(CASE WHEN [上午得分]=20 AND [下午得分]=20 THEN 2 WHEN [上午得分]=20 AND [下午得分]<>20 THEN 1
WHEN [上午得分]<>20 AND [下午得分]=20 THEN 1 ELSE 0 END )
,[30分]=SUM(CASE WHEN [上午得分]=30 AND [下午得分]=30 THEN 2 WHEN [上午得分]=30 AND [下午得分]<>30 THEN 1
WHEN [上午得分]<>30 AND [下午得分]=30 THEN 1 ELSE 0 END )
from [huang]
GROUP BY [姓名]
ORDER BY [姓名] DESC
----------------结果----------------------------
/*
姓名 0分 10分 20分 30分
---- ----------- ----------- ----------- -----------
张三 1 2 1 0
李四 3 0 2 1
*/
--> 测试数据: @T
declare @T table (姓名 varchar(4),上午得分 int,下午得分 int)
insert into @T
select '张三',10,20 union all
select '张三',0,10 union all
select '李四',20,0 union all
select '李四',0,0 union all
select '李四',30,20
;with m as
(
select 姓名,上午得分 col from @T
union all
select 姓名,下午得分 from @T
)
select 姓名,
sum(case when col=0 then 1 else 0 end) as [0分],
sum(case when col=10 then 1 else 0 end) as [10分],
sum(case when col=20 then 1 else 0 end) as [20分],
sum(case when col=30 then 1 else 0 end) as [30分]
from m group by 姓名 order by 姓名 desc
/*
姓名 0分 10分 20分 30分
---- ----------- ----------- ----------- -----------
张三 1 2 1 0
李四 3 0 2 1
*/
select 姓名,
sum(case defen/10 when 0 then 1 else 0 end) as [0分],
sum(case defen/10 when 1 then 1 else 0 end) as [10分],
sum(case defen/10 when 2 then 1 else 0 end) as [20分],
sum(case defen/10 when 3 then 1 else 0 end) as [30分]
from (
select 姓名,defen=上午得分 from tb
union all
select 姓名,defen=下午得分 from tb
) m group by 姓名 order by 姓名 desc
