--等級怎麼算的??沒說清楚
--沒測試,隨手寫的,可能有錯
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,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
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)
GOinsert 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, 4CREATE TABLE B(userid char(3),username char(10))
GOinsert 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))
GOinsert 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
楼主各位少了一个条件group by 要加上 year(cdata).
不加会把其它年份同一季度的加在一起
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,4declare @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'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 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 Descupdate 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 tselect * 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 @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,4declare @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
*/
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, 4declare @用户表 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 行)
排行上50%不包含前5%为2级......
语句多但简单是由以下语句为基础.记录总数的百分比得出的结果
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)from @人员记录 a,@用户表 b,@部门表 c
where a.userid=b.userid and a.sectionid=c.sectionid
group by username,sectionidname order by 总计 desc
如果有其它年份在group by 加上一个 year(cdata)