数据汇总后按数据分配等级，如何写sql语句？谢谢。十万火急！！
有三个表
A --记录了人员得分情况。
userid cdata number sectionid
------------ ------------ ------ -----------------
001 2006-01-20 1 3
001 2006-02-20 1 3
002 2006-01-20 1 1
006 2006-04-20 1 2
002 2006-05-20 1 1
001 2006-07-20 1 3
005 2006-08-20 1 4
……
B --用户表
userid username
---------- ------------
001 aaa
002 vvv
005 ddd
006 eee
……
C --部门表
sectionid sectionidname
---------- ------------
1 sdfg
2 5635
3 afas
4 dfad
……
我如何实现下面的结果：对表b中的每个人按表a的数值分季度和全年进行汇总，然后按照总分有大到小，对表b人总数的5%显示等级为1，45%等级为2，35%等级为3，5%等级为4。
最终显示结果如下表。
username sectionidname 第一季度 第二季度 第三季度 第四季度 总计 等级
aaa sdfg 10 20 20 30 80 1
请各位朋友帮帮忙，小弟现行谢过。
[解决办法]
--等級怎麼算的??沒說清楚
--沒測試,隨手寫的,可能有錯
select B.username,C.sectionidname,T.第一季度,T.第二季度,T.第三季度,T.第四季度,T.总计
from B
left join (select userid,sectionid
sum(case when month(cdata) between 1 and 3 then number else 0 end) as 第一季度,
sum(case when month(cdata) between 4 and 6 then number else 0 end) as 第二季度,
sum(case when month(cdata) between 7 and 9 then number else 0 end) as 第三季度,
sum(case when month(cdata) between 10 and 12 then number else 0 end) as 第四季度,
sum(number) as 总计
from A group by userid,sectionid
) T on B.userid=T.userid
inner join C on B.sectionid=C.sectionid
[解决办法]
先汇总一下数据，等级划分稍后再说：
select
B.username,C.sectionidname,
sum(case datepart(qq,A.cdata) when 1 then A.number else 0 end) as Q1,
sum(case datepart(qq,A.cdata) when 2 then A.number else 0 end) as Q2,
sum(case datepart(qq,A.cdata) when 3 then A.number else 0 end) as Q3,
sum(case datepart(qq,A.cdata) when 4 then A.number else 0 end) as Q4,
sum(A.number) as Cnt
from
A,B,C
where
A.userid=B.userid and A.sectionid=C.sectionid and year(A.cdata)=2006
group by
B.username,C.sectionidname
[解决办法]
----建表
create table A(
userid char(3),
cdata char(10),
number int,
sectionid int)
GO
insert into A
select '001 ', '2006-01-20 ', 1, 3 UNION ALL
select '001 ', '2006-02-20 ', 1, 3 UNION ALL
select '002 ', '2006-01-20 ', 1, 1 UNION ALL
select '006 ', '2006-04-20 ', 1, 2 UNION ALL
select '002 ', '2006-05-20 ', 1, 1 UNION ALL
select '001 ', '2006-07-20 ', 1, 3 UNION ALL
select '005 ', '2006-08-20 ', 1, 4
CREATE TABLE B(userid char(3),username char(10))
GO
insert into B
select '001 ', 'aaa ' UNION ALL
select '002 ', 'vvv ' UNION ALL
select '005 ', 'ddd ' UNION ALL
select '006 ', 'eee '
CREATE TABLE C(sectionid int,sectionidname char(10))
GO
insert into C
SELECT 1, 'sdfg ' UNION ALL
SELECT 2, '5635 ' UNION ALL
SELECT 3, 'afas ' UNION ALL
SELECT 4, 'dfad '
select username ,sectionidname,
sum((case when substring(cdata,6,2) in ( '01 ', '02 ', '03 ') THEN number else 0 end)) as 第一季度,
sum((case when substring(cdata,6,2) in ( '04 ', '05 ', '06 ') THEN number else 0 end)) as 第二季度,
sum((case when substring(cdata,6,2) in ( '07 ', '08 ', '09 ') THEN number else 0 end)) as 第三季度,
sum((case when substring(cdata,6,2) in ( '10 ', '11 ', '12 ') THEN number else 0 end)) as 第四季度,
sum(number) as 总计
from A,B,C WHERE A.userid=B.userid AND A.sectionid=C.sectionid
group by username ,sectionidname
[解决办法]
--定义表变量，并Insert测试数据
declare @A table(userid varchar(10),cdata datetime,number int,sectionid int)
insert into @A select '001 ', '2006-01-20 ',1,3
insert into @A select '001 ', '2006-02-20 ',1,3
insert into @A select '002 ', '2006-01-20 ',1,1
insert into @A select '006 ', '2006-04-20 ',1,2
insert into @A select '002 ', '2006-05-20 ',1,1
insert into @A select '001 ', '2006-07-20 ',1,3
insert into @A select '005 ', '2006-08-20 ',1,4
declare @B table(userid varchar(10),username varchar(10))
insert into @B select '001 ', 'aaa '
insert into @B select '002 ', 'vvv '
insert into @B select '005 ', 'ddd '
insert into @B select '006 ', 'eee '
declare @C table(sectionid int,sectionidname varchar(10))
insert into @C select 1, 'sdfg '
insert into @C select 2, '5635 '
insert into @C select 3, 'afas '
insert into @C select 4, 'dfad '
--定义存储中间数据及最终结果数据的表变量@D
declare @D table(id int identity(1,1),username varchar(10),sectionidname varchar(10),Q1 int,Q2 int,Q3 int,Q4 int,Cnt int,Rank int)
--将汇总的中间数据Insert到表变量@D
insert into @D(username,sectionidname,Q1,Q2,Q3,Q4,Cnt)
select
B.username,C.sectionidname,
sum(case datepart(qq,A.cdata) when 1 then A.number else 0 end) as Q1,
sum(case datepart(qq,A.cdata) when 2 then A.number else 0 end) as Q2,
sum(case datepart(qq,A.cdata) when 3 then A.number else 0 end) as Q3,
sum(case datepart(qq,A.cdata) when 4 then A.number else 0 end) as Q4,
sum(A.number) as Cnt
from
@A A,@B B,@C C
where
A.userid=B.userid and A.sectionid=C.sectionid and year(A.cdata)=2006
group by
B.username,C.sectionidname
order by
Cnt Desc
--根据预定规则排列数据的等级Rank
update t
set
Rank=(case
when t.id in(select top 5 percent WITH TIES id from @D order by Cnt Desc) then 1
when t.id in(select top 50 percent WITH TIES id from @D order by Cnt Desc) then 2
when t.id in(select top 85 percent WITH TIES id from @D order by Cnt Desc) then 3
when t.id in(select top 90 percent WITH TIES id from @D order by Cnt Desc) then 4
else 5
end)
from
@D t
--查看执行结果
select * from @D
/*
id username sectionidname Q1 Q2 Q3 Q4 Cnt Rank
---- ---------- ------------- ----- ----- ----- ----- ----- ------
1 aaa afas 2 0 1 0 3 1
2 vvv sdfg 1 1 0 0 2 2
3 ddd dfad 0 0 1 0 1 3
4 eee 5635 0 1 0 0 1 3
*/
[解决办法]
declare @人员记录 table(userid varchar(3) , cdata datetime, number int, sectionid int)
insert @人员记录
select '001 ', '2006-01-20 ', 1, 3
union all select '001 ', '2006-02-20 ', 1, 3
union all select '002 ', '2006-01-20 ', 1, 1
union all select '006 ', '2006-04-20 ', 1, 2
union all select '002 ', '2006-05-20 ', 1, 1
union all select '001 ', '2006-07-20 ', 1, 3
union all select '005 ', '2006-08-20 ', 1, 4
declare @用户表 table(userid varchar(3), username varchar(3))
insert @用户表
select '001 ', 'aaa '
union all select '002 ', 'vvv '
union all select '005 ', 'ddd '
union all select '006 ', 'eee '
declare @部门表 table(sectionid int, sectionidname varchar(5))
insert @部门表
select 1, 'sdfg '
union all select 2, '5635 '
union all select 3, 'afas '
union all select 4, 'dfad '
--username sectionidname 第一季度 第二季度 第三季度 第四季度 总计 等级
--(select * from (select top 5 percent username
--from @人员记录 a,@用户表 b,@部门表 c
--where a.userid=b.userid and a.sectionid=c.sectionid
--group by username,sectionidname order by sum(number) desc)ta where username=b.username)
select username,sectionidname,
[第一季度]=sum(case when month(cdata) between 1 and 3 then number else 0 end),
[第二季度]=sum(case when month(cdata) between 4 and 6 then number else 0 end),
[第三季度]=sum(case when month(cdata) between 7 and 9 then number else 0 end),
[第四季度]=sum(case when month(cdata) between 10 and 12 then number else 0 end),
[总计]=sum(number),
[等级]=case when exists(select username from (select top 5 percent username from @人员记录 a,@用户表 b,@部门表 c
where a.userid=b.userid and a.sectionid=c.sectionid
group by username,sectionidname order by sum(number) desc)ta where username=b.username)
then '1级 '
when not exists (select username from (select top 5 percent username from @人员记录 a,@用户表 b,@部门表 c
where a.userid=b.userid and a.sectionid=c.sectionid
group by username,sectionidname order by sum(number) desc)ta where username=b.username)
and exists(select username from (select top 50 percent username from @人员记录 a,@用户表 b,@部门表 c
where a.userid=b.userid and a.sectionid=c.sectionid
group by username,sectionidname order by sum(number) desc)ta where username=b.username)
then '2级 '
when not exists(select username from (select top 50 percent username from @人员记录 a,@用户表 b,@部门表 c
where a.userid=b.userid and a.sectionid=c.sectionid
group by username,sectionidname order by sum(number) desc)ta where username=b.username) and
exists(select username from (select top 85 percent username from @人员记录 a,@用户表 b,@部门表 c
where a.userid=b.userid and a.sectionid=c.sectionid
group by username,sectionidname order by sum(number) desc)ta where username=b.username)
then '3级 '
when not exists(select username from (select top 85 percent username from @人员记录 a,@用户表 b,@部门表 c
where a.userid=b.userid and a.sectionid=c.sectionid
group by username,sectionidname order by sum(number) desc)ta where username=b.username) and
exists(select username from (select top 90 percent username from @人员记录 a,@用户表 b,@部门表 c
where a.userid=b.userid and a.sectionid=c.sectionid
group by username,sectionidname order by sum(number) desc)ta where username=b.username)
then '4级 '
else '无级 ' end
from @人员记录 a,@用户表 b,@部门表 c
where a.userid=b.userid and a.sectionid=c.sectionid
group by username,sectionidname order by 总计 desc
（所影响的行数为 4 行）
username sectionidname 第一季度 第二季度 第三季度 第四季度 总计 等级
-------- ------------- ----------- ----------- ----------- ----------- ----------- ----
aaa afas 2 0 1 0 3 1级
vvv sdfg 1 1 0 0 2 2级
eee 5635 0 1 0 0 1 3级
ddd dfad 0 0 1 0 1 3级
（所影响的行数为 4 行）
