--假设按前两名算,最多排前六名. create table tb(id int, px int,rq varchar(10)) insert into tb values(1,1,'2009-01-01') insert into tb values(2,2,'2009-01-01') insert into tb values(3,3,'2009-01-01') insert into tb values(4,4,'2009-01-01') insert into tb values(5,5,'2009-01-01') insert into tb values(6,6,'2009-01-01') insert into tb values(1,2,'2009-01-02') insert into tb values(2,1,'2009-01-02') insert into tb values(3,5,'2009-01-02') insert into tb values(4,4,'2009-01-02') insert into tb values(5,3,'2009-01-02') insert into tb values(6,6,'2009-01-02') insert into tb values(1,2,'2009-01-03') insert into tb values(2,1,'2009-01-03') insert into tb values(3,3,'2009-01-03') insert into tb values(4,4,'2009-01-03') insert into tb values(5,5,'2009-01-03') insert into tb values(6,6,'2009-01-03') insert into tb values(1,1,'2009-01-04') insert into tb values(2,2,'2009-01-04') insert into tb values(3,3,'2009-01-04') insert into tb values(4,4,'2009-01-04') insert into tb values(5,5,'2009-01-04') insert into tb values(6,6,'2009-01-04') insert into tb values(1,1,'2009-01-05') insert into tb values(2,2,'2009-01-05') insert into tb values(3,3,'2009-01-05') insert into tb values(4,4,'2009-01-05') insert into tb values(5,5,'2009-01-05') insert into tb values(6,6,'2009-01-05') insert into tb values(1,3,'2009-01-06') insert into tb values(2,2,'2009-01-06') insert into tb values(3,1,'2009-01-06') insert into tb values(4,4,'2009-01-06') insert into tb values(5,5,'2009-01-06') insert into tb values(6,6,'2009-01-06') goselect m.id , sum(case px when 1 then 1 else 0 end) [1], sum(case px when 2 then 1 else 0 end) [2], sum(case px when 3 then 1 else 0 end) [3], sum(case px when 4 then 1 else 0 end) [4], sum(case px when 5 then 1 else 0 end) [5], sum(case px when 6 then 1 else 0 end) [6] from tb m , (select top 2 id , count(*) cnt from tb where px = 1 group by id order by cnt desc) n where m.id = n.id group by m.iddrop table tb/* id 1 2 3 4 5 6 ----------- ----------- ----------- ----------- ----------- ----------- ----------- 1 3 2 1 0 0 0 2 2 4 0 0 0 0(所影响的行数为 2 行) */
with TMP as (select top 20 学号, 名次, count(1) cn from Table_Name where 名次=1 group by 学号, 名次 order by count(1) desc) select a.学号, a.名次, count(1) cn from Table_Name a inner join TMP b on a.学号=b.学号 group by a.学号, a.名次
--模拟数据 declare @Table table (id int,xuehao int,mingci int,inserttime varchar(12)) insert into @Table select 1,1,1,'2009-2-4' union all select 2,3,2,'2009-2-4' union all select 3,4,3,'2009-2-4' union all select 4,2,4,'2009-2-4' union all select 5,5,5,'2009-2-4' union all select 6,3,1,'2009-2-5' union all select 7,4,2,'2009-2-5' union all select 8,1,3,'2009-2-5' union all select 9,2,4,'2009-2-5' union all select 10,5,5,'2009-2-5' union all select 11,3,1,'2009-2-6' union all select 12,1,2,'2009-2-6' union all select 13,4,3,'2009-2-6' union all select 14,5,4,'2009-2-6' union all select 15,2,5,'2009-2-6' union all select 16,3,1,'2009-2-7' union all select 17,1,2,'2009-2-7' union all select 18,4,3,'2009-2-7' union all select 19,2,4,'2009-2-7' union all select 20,5,5,'2009-2-7'select * from @Table /* 模拟5个人,4天的记录 */ select bb.* from ( select xuehao, 第一名次数=(select count(*) from @Table where mingci=1 and xuehao=a.xuehao) , 第二名次数=(select count(*) from @Table where mingci=2 and xuehao=a.xuehao) , 第三名次数=(select count(*) from @Table where mingci=3 and xuehao=a.xuehao) , 第四名次数=(select count(*) from @Table where mingci=4 and xuehao=a.xuehao) , 第五名次数=(select count(*) from @Table where mingci=5 and xuehao=a.xuehao) from @Table a group by xuehao ) bb order by 第一名次数 desc /* xuehao 第一名次数 第二名次数 第三名次数 第四名次数 第五名次数 ----------- ----------- ----------- ----------- ----------- ----------- 3 3 1 0 0 0 1 1 2 1 0 0 2 0 0 0 3 1 4 0 1 3 0 0 5 0 0 0 1 3 */
set nocount on--生成测试数据 if object_id('top20') is not null drop table top20 create table top20 (date datetime, D_rank int, SU_id int)declare @n int declare @D datetime declare @SU_ID int declare @T bitset @n = 1 set @D ='01/05/2009' set @T =1while @D < dateadd(dy,60,'01/05/2009') begin --产生六十天的测试数据 while @n <= 20 begin while @T =1 begin set @SU_ID = 1+abs(checksum(newid()))%51 if not exists(select 1 from top20 where date=@D and SU_id=@SU_ID) begin set @T=0 insert into top20 select @D,@n,@su_ID end end set @n=@n+1 set @T=1 end set @D = dateadd(dy,1,@D) set @n=1end --统计 select top 20 rank() over(order by count(D_rank) desc) as RankId, SU_id, D_rank, N=count(D_rank) into #tmp from top20 where D_rank=1 group by SU_id,D_rank order by D_rank,N desc select a.SU_ID, a.D_rank, N=count(a.D_rank) into #tmp2 from top20 a left join #tmp b on a.SU_ID=b.SU_ID where a.D_rank<=10 group by a.SU_ID,a.D_rank select [学号]=a.SU_ID, [1]=b.no_1,[2]=no_2,[3]=no_3,[4]=no_4,[5]=no_5,[6]=no_6,[7]=no_7,[8]=no_8,[9]=no_9,[10]=no_10 from #tmp a left join ( select c.SU_ID ,no_1=sum(case when c.D_rank=1 then N else 0 end) ,no_2=sum(case when c.D_rank=2 then N else 0 end) ,no_3=sum(case when c.D_rank=3 then N else 0 end) ,no_4=sum(case when c.D_rank=4 then N else 0 end) ,no_5=sum(case when c.D_rank=5 then N else 0 end) ,no_6=sum(case when c.D_rank=6 then N else 0 end) ,no_7=sum(case when c.D_rank=7 then N else 0 end) ,no_8=sum(case when c.D_rank=8 then N else 0 end) ,no_9=sum(case when c.D_rank=9 then N else 0 end) ,no_10=sum(case when c.D_rank=10 then N else 0 end) from #tmp2 c group by SU_ID ) b on a.SU_ID=b.SU_ID drop table #tmp,#tmp2 set nocount off /* --随机结果 学号 1 2 3 4 5 6 7 8 9 10 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 25 5 0 2 1 0 1 0 2 1 0 7 4 3 0 1 0 0 0 0 1 0 9 3 2 2 0 2 3 0 1 2 2 12 3 1 0 1 1 2 0 0 3 0 18 3 1 0 1 2 3 1 0 1 2 42 3 4 0 2 1 1 1 3 0 2 38 2 3 0 1 2 1 0 2 2 0 23 2 1 0 0 0 0 2 0 0 3 33 2 0 3 0 0 1 1 1 2 0 16 2 2 2 4 2 1 2 3 1 3 11 2 1 1 4 1 4 1 1 3 0 2 2 1 2 2 1 1 0 0 1 1 5 2 1 4 0 1 0 1 0 0 1 6 1 3 0 0 2 2 2 1 4 0 3 1 0 1 1 1 1 1 0 2 2 4 1 2 1 0 0 1 2 1 1 2 1 1 2 1 1 1 0 1 1 0 1 10 1 0 1 4 0 0 3 0 2 0 17 1 2 1 1 1 0 1 1 0 1 20 1 0 1 2 2 3 0 1 1 1 */
:)忘了用代码格式,再贴一遍 set nocount on--生成测试数据 if object_id('top20') is not null drop table top20 create table top20 (date datetime, D_rank int, SU_id int)declare @n int declare @D datetime declare @SU_ID int declare @T bitset @n = 1 set @D ='01/05/2009' set @T =1while @D < dateadd(dy,60,'01/05/2009') begin --产生六十天的测试数据 while @n <= 20 begin while @T =1 begin set @SU_ID = 1+abs(checksum(newid()))%51 if not exists(select 1 from top20 where date=@D and SU_id=@SU_ID) begin set @T=0 insert into top20 select @D,@n,@su_ID end end set @n=@n+1 set @T=1 end set @D = dateadd(dy,1,@D) set @n=1end --统计 select top 20 rank() over(order by count(D_rank) desc) as RankId, SU_id, D_rank, N=count(D_rank) into #tmp from top20 where D_rank=1 group by SU_id,D_rank order by D_rank,N desc select a.SU_ID, a.D_rank, N=count(a.D_rank) into #tmp2 from top20 a left join #tmp b on a.SU_ID=b.SU_ID where a.D_rank<=10 group by a.SU_ID,a.D_rank select [学号]=a.SU_ID, [1]=b.no_1,[2]=no_2,[3]=no_3,[4]=no_4,[5]=no_5,[6]=no_6,[7]=no_7,[8]=no_8,[9]=no_9,[10]=no_10 from #tmp a left join ( select c.SU_ID ,no_1=sum(case when c.D_rank=1 then N else 0 end) ,no_2=sum(case when c.D_rank=2 then N else 0 end) ,no_3=sum(case when c.D_rank=3 then N else 0 end) ,no_4=sum(case when c.D_rank=4 then N else 0 end) ,no_5=sum(case when c.D_rank=5 then N else 0 end) ,no_6=sum(case when c.D_rank=6 then N else 0 end) ,no_7=sum(case when c.D_rank=7 then N else 0 end) ,no_8=sum(case when c.D_rank=8 then N else 0 end) ,no_9=sum(case when c.D_rank=9 then N else 0 end) ,no_10=sum(case when c.D_rank=10 then N else 0 end) from #tmp2 c group by SU_ID ) b on a.SU_ID=b.SU_ID drop table #tmp,#tmp2 set nocount off/* 学号 1 2 3 4 5 6 7 8 9 10 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 25 5 0 2 1 0 1 0 2 1 0 7 4 3 0 1 0 0 0 0 1 0 9 3 2 2 0 2 3 0 1 2 2 12 3 1 0 1 1 2 0 0 3 0 18 3 1 0 1 2 3 1 0 1 2 42 3 4 0 2 1 1 1 3 0 2 38 2 3 0 1 2 1 0 2 2 0 23 2 1 0 0 0 0 2 0 0 3 33 2 0 3 0 0 1 1 1 2 0 16 2 2 2 4 2 1 2 3 1 3 11 2 1 1 4 1 4 1 1 3 0 2 2 1 2 2 1 1 0 0 1 1 5 2 1 4 0 1 0 1 0 0 1 6 1 3 0 0 2 2 2 1 4 0 3 1 0 1 1 1 1 1 0 2 2 4 1 2 1 0 0 1 2 1 1 2 1 1 2 1 1 1 0 1 1 0 1 10 1 0 1 4 0 0 3 0 2 0 17 1 2 1 1 1 0 1 1 0 1 20 1 0 1 2 2 3 0 1 1 1 */
create table tb(id int, px int,rq varchar(10))
insert into tb values(1,1,'2009-01-01')
insert into tb values(2,2,'2009-01-01')
insert into tb values(3,3,'2009-01-01')
insert into tb values(4,4,'2009-01-01')
insert into tb values(5,5,'2009-01-01')
insert into tb values(6,6,'2009-01-01')
insert into tb values(1,2,'2009-01-02')
insert into tb values(2,1,'2009-01-02')
insert into tb values(3,5,'2009-01-02')
insert into tb values(4,4,'2009-01-02')
insert into tb values(5,3,'2009-01-02')
insert into tb values(6,6,'2009-01-02')
insert into tb values(1,2,'2009-01-03')
insert into tb values(2,1,'2009-01-03')
insert into tb values(3,3,'2009-01-03')
insert into tb values(4,4,'2009-01-03')
insert into tb values(5,5,'2009-01-03')
insert into tb values(6,6,'2009-01-03')
insert into tb values(1,1,'2009-01-04')
insert into tb values(2,2,'2009-01-04')
insert into tb values(3,3,'2009-01-04')
insert into tb values(4,4,'2009-01-04')
insert into tb values(5,5,'2009-01-04')
insert into tb values(6,6,'2009-01-04')
insert into tb values(1,1,'2009-01-05')
insert into tb values(2,2,'2009-01-05')
insert into tb values(3,3,'2009-01-05')
insert into tb values(4,4,'2009-01-05')
insert into tb values(5,5,'2009-01-05')
insert into tb values(6,6,'2009-01-05')
insert into tb values(1,3,'2009-01-06')
insert into tb values(2,2,'2009-01-06')
insert into tb values(3,1,'2009-01-06')
insert into tb values(4,4,'2009-01-06')
insert into tb values(5,5,'2009-01-06')
insert into tb values(6,6,'2009-01-06')
goselect m.id ,
sum(case px when 1 then 1 else 0 end) [1],
sum(case px when 2 then 1 else 0 end) [2],
sum(case px when 3 then 1 else 0 end) [3],
sum(case px when 4 then 1 else 0 end) [4],
sum(case px when 5 then 1 else 0 end) [5],
sum(case px when 6 then 1 else 0 end) [6]
from tb m ,
(select top 2 id , count(*) cnt from tb where px = 1 group by id order by cnt desc) n
where m.id = n.id
group by m.iddrop table tb/*
id 1 2 3 4 5 6
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 3 2 1 0 0 0
2 2 4 0 0 0 0(所影响的行数为 2 行)
*/
select a.学号, a.名次, count(1) cn from Table_Name a inner join TMP b on a.学号=b.学号 group by a.学号, a.名次
--模拟数据
declare @Table table (id int,xuehao int,mingci int,inserttime varchar(12))
insert into @Table
select 1,1,1,'2009-2-4' union all
select 2,3,2,'2009-2-4' union all
select 3,4,3,'2009-2-4' union all
select 4,2,4,'2009-2-4' union all
select 5,5,5,'2009-2-4' union all
select 6,3,1,'2009-2-5' union all
select 7,4,2,'2009-2-5' union all
select 8,1,3,'2009-2-5' union all
select 9,2,4,'2009-2-5' union all
select 10,5,5,'2009-2-5' union all
select 11,3,1,'2009-2-6' union all
select 12,1,2,'2009-2-6' union all
select 13,4,3,'2009-2-6' union all
select 14,5,4,'2009-2-6' union all
select 15,2,5,'2009-2-6' union all
select 16,3,1,'2009-2-7' union all
select 17,1,2,'2009-2-7' union all
select 18,4,3,'2009-2-7' union all
select 19,2,4,'2009-2-7' union all
select 20,5,5,'2009-2-7'select * from @Table
/*
模拟5个人,4天的记录
*/
select bb.* from (
select xuehao,
第一名次数=(select count(*) from @Table where mingci=1 and xuehao=a.xuehao) ,
第二名次数=(select count(*) from @Table where mingci=2 and xuehao=a.xuehao) ,
第三名次数=(select count(*) from @Table where mingci=3 and xuehao=a.xuehao) ,
第四名次数=(select count(*) from @Table where mingci=4 and xuehao=a.xuehao) ,
第五名次数=(select count(*) from @Table where mingci=5 and xuehao=a.xuehao)
from @Table a group by xuehao
) bb
order by 第一名次数 desc
/*
xuehao 第一名次数 第二名次数 第三名次数 第四名次数 第五名次数
----------- ----------- ----------- ----------- ----------- -----------
3 3 1 0 0 0
1 1 2 1 0 0
2 0 0 0 3 1
4 0 1 3 0 0
5 0 0 0 1 3
*/
if object_id('top20') is not null drop table top20
create table top20 (date datetime, D_rank int, SU_id int)declare @n int
declare @D datetime
declare @SU_ID int
declare @T bitset @n = 1
set @D ='01/05/2009'
set @T =1while @D < dateadd(dy,60,'01/05/2009') begin --产生六十天的测试数据
while @n <= 20 begin
while @T =1 begin
set @SU_ID = 1+abs(checksum(newid()))%51
if not exists(select 1 from top20 where date=@D and SU_id=@SU_ID) begin
set @T=0
insert into top20 select @D,@n,@su_ID
end
end
set @n=@n+1
set @T=1
end
set @D = dateadd(dy,1,@D)
set @n=1end
--统计
select top 20 rank() over(order by count(D_rank) desc) as RankId, SU_id, D_rank, N=count(D_rank) into #tmp from top20 where D_rank=1 group by SU_id,D_rank order by D_rank,N desc
select a.SU_ID, a.D_rank, N=count(a.D_rank) into #tmp2 from top20 a left join #tmp b on a.SU_ID=b.SU_ID where a.D_rank<=10 group by a.SU_ID,a.D_rank
select [学号]=a.SU_ID, [1]=b.no_1,[2]=no_2,[3]=no_3,[4]=no_4,[5]=no_5,[6]=no_6,[7]=no_7,[8]=no_8,[9]=no_9,[10]=no_10
from #tmp a left join
(
select c.SU_ID
,no_1=sum(case when c.D_rank=1 then N else 0 end)
,no_2=sum(case when c.D_rank=2 then N else 0 end)
,no_3=sum(case when c.D_rank=3 then N else 0 end)
,no_4=sum(case when c.D_rank=4 then N else 0 end)
,no_5=sum(case when c.D_rank=5 then N else 0 end)
,no_6=sum(case when c.D_rank=6 then N else 0 end)
,no_7=sum(case when c.D_rank=7 then N else 0 end)
,no_8=sum(case when c.D_rank=8 then N else 0 end)
,no_9=sum(case when c.D_rank=9 then N else 0 end)
,no_10=sum(case when c.D_rank=10 then N else 0 end)
from #tmp2 c group by SU_ID ) b on a.SU_ID=b.SU_ID
drop table #tmp,#tmp2
set nocount off
/*
--随机结果
学号 1 2 3 4 5 6 7 8 9 10
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
25 5 0 2 1 0 1 0 2 1 0
7 4 3 0 1 0 0 0 0 1 0
9 3 2 2 0 2 3 0 1 2 2
12 3 1 0 1 1 2 0 0 3 0
18 3 1 0 1 2 3 1 0 1 2
42 3 4 0 2 1 1 1 3 0 2
38 2 3 0 1 2 1 0 2 2 0
23 2 1 0 0 0 0 2 0 0 3
33 2 0 3 0 0 1 1 1 2 0
16 2 2 2 4 2 1 2 3 1 3
11 2 1 1 4 1 4 1 1 3 0
2 2 1 2 2 1 1 0 0 1 1
5 2 1 4 0 1 0 1 0 0 1
6 1 3 0 0 2 2 2 1 4 0
3 1 0 1 1 1 1 1 0 2 2
4 1 2 1 0 0 1 2 1 1 2
1 1 2 1 1 1 0 1 1 0 1
10 1 0 1 4 0 0 3 0 2 0
17 1 2 1 1 1 0 1 1 0 1
20 1 0 1 2 2 3 0 1 1 1
*/
set nocount on--生成测试数据
if object_id('top20') is not null drop table top20
create table top20 (date datetime, D_rank int, SU_id int)declare @n int
declare @D datetime
declare @SU_ID int
declare @T bitset @n = 1
set @D ='01/05/2009'
set @T =1while @D < dateadd(dy,60,'01/05/2009') begin --产生六十天的测试数据
while @n <= 20 begin
while @T =1 begin
set @SU_ID = 1+abs(checksum(newid()))%51
if not exists(select 1 from top20 where date=@D and SU_id=@SU_ID) begin
set @T=0
insert into top20 select @D,@n,@su_ID
end
end
set @n=@n+1
set @T=1
end
set @D = dateadd(dy,1,@D)
set @n=1end
--统计
select top 20 rank() over(order by count(D_rank) desc) as RankId, SU_id, D_rank, N=count(D_rank) into #tmp from top20 where D_rank=1 group by SU_id,D_rank order by D_rank,N desc
select a.SU_ID, a.D_rank, N=count(a.D_rank) into #tmp2 from top20 a left join #tmp b on a.SU_ID=b.SU_ID where a.D_rank<=10 group by a.SU_ID,a.D_rank
select [学号]=a.SU_ID, [1]=b.no_1,[2]=no_2,[3]=no_3,[4]=no_4,[5]=no_5,[6]=no_6,[7]=no_7,[8]=no_8,[9]=no_9,[10]=no_10
from #tmp a left join
(
select c.SU_ID
,no_1=sum(case when c.D_rank=1 then N else 0 end)
,no_2=sum(case when c.D_rank=2 then N else 0 end)
,no_3=sum(case when c.D_rank=3 then N else 0 end)
,no_4=sum(case when c.D_rank=4 then N else 0 end)
,no_5=sum(case when c.D_rank=5 then N else 0 end)
,no_6=sum(case when c.D_rank=6 then N else 0 end)
,no_7=sum(case when c.D_rank=7 then N else 0 end)
,no_8=sum(case when c.D_rank=8 then N else 0 end)
,no_9=sum(case when c.D_rank=9 then N else 0 end)
,no_10=sum(case when c.D_rank=10 then N else 0 end)
from #tmp2 c group by SU_ID ) b on a.SU_ID=b.SU_ID
drop table #tmp,#tmp2
set nocount off/*
学号 1 2 3 4 5 6 7 8 9 10
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
25 5 0 2 1 0 1 0 2 1 0
7 4 3 0 1 0 0 0 0 1 0
9 3 2 2 0 2 3 0 1 2 2
12 3 1 0 1 1 2 0 0 3 0
18 3 1 0 1 2 3 1 0 1 2
42 3 4 0 2 1 1 1 3 0 2
38 2 3 0 1 2 1 0 2 2 0
23 2 1 0 0 0 0 2 0 0 3
33 2 0 3 0 0 1 1 1 2 0
16 2 2 2 4 2 1 2 3 1 3
11 2 1 1 4 1 4 1 1 3 0
2 2 1 2 2 1 1 0 0 1 1
5 2 1 4 0 1 0 1 0 0 1
6 1 3 0 0 2 2 2 1 4 0
3 1 0 1 1 1 1 1 0 2 2
4 1 2 1 0 0 1 2 1 1 2
1 1 2 1 1 1 0 1 1 0 1
10 1 0 1 4 0 0 3 0 2 0
17 1 2 1 1 1 0 1 1 0 1
20 1 0 1 2 2 3 0 1 1 1
*/