表中存以下数据:
id courseid userid score num1164 B0422A hux990806 100 3
1166 B0422A hux990806 100 3
1165 B0422A hux990806 100 3
782 B1012A hanwenlong 85 5
584 B1012A hanwenlong 85 5
785 B1012A hanwenlong 75 5
583 B1012A hanwenlong 75 5
582 B1012A hanwenlong 65 5
586 B1012A hanwenlong 65 5 例如:userid='hanwenlong' courseid='B1012A' 这样的数据num为5
我要从结果集中取score 最大的前5条请不要使用游标解决这个问题,因为这个表中的数据量很大,执行起来太慢了,请哪位高手给出具体的SQL语句
id courseid userid score num1164 B0422A hux990806 100 3
1166 B0422A hux990806 100 3
1165 B0422A hux990806 100 3
782 B1012A hanwenlong 85 5
584 B1012A hanwenlong 85 5
785 B1012A hanwenlong 75 5
583 B1012A hanwenlong 75 5
582 B1012A hanwenlong 65 5
586 B1012A hanwenlong 65 5 例如:userid='hanwenlong' courseid='B1012A' 这样的数据num为5
我要从结果集中取score 最大的前5条请不要使用游标解决这个问题,因为这个表中的数据量很大,执行起来太慢了,请哪位高手给出具体的SQL语句
是这个意思吗?
where userid='hanwenlong' and courseid='B1012A'
order by score desc --加上order by
distinct b.*
from
tb a
cross apply
(select top 5 * from tb where userid='hanwenlong' and courseid='B1012A' and courseid=a.courseid order by score)b
distinct b.*
from
tb a
cross apply
(select top 5 * from tb where userid='hanwenlong' and courseid='B1012A' and courseid=a.courseid order by score DESC)b
select * from (
select rid=ROW_NUMBER() over (partition by courseid,userid order by score desc),* from [tb]
) t
where userid='hanwenlong' and courseid='B1012A' and rid<=5/*
rid id courseid userid score num
-------------------- ----------- -------- ---------- ----------- -----------
1 782 B1012A hanwenlong 85 5
2 584 B1012A hanwenlong 85 5
3 785 B1012A hanwenlong 75 5
4 583 B1012A hanwenlong 75 5
5 582 B1012A hanwenlong 65 5(5 行受影响)
不是,我那个表中存在两部分数据,我用颜色标识出来了!每一部分颜色的数据我需要以courseid,userid分组,根据num数值去对应的记录数,我举个例子:
id courseid userid score num
1164 B0422A hux990806 100 3
1166 B0422A hux990806 100 3
1165 B0422A hux990806 100 3 这个部分数据num是3,所以需要根据courseid、userid一组取出其中的3条数据782 B1012A hanwenlong 85 5
584 B1012A hanwenlong 85 5
785 B1012A hanwenlong 75 5
583 B1012A hanwenlong 75 5
582 B1012A hanwenlong 65 5
586 B1012A hanwenlong 65 5 这个部分数据num是5,所以需要根据courseid、userid一组取出其中的5条数不知道我这样讲,大家明不明白
1166 B0422A hux990806 100 3
1165 B0422A hux990806 100 3
782 B1012A hanwenlong 85 5
584 B1012A hanwenlong 85 5
785 B1012A hanwenlong 75 5
583 B1012A hanwenlong 75 5
582 B1012A hanwenlong 65 5
586 B1012A hanwenlong 65 5 create table #tt(id int, courseid varchar(20),userid varchar(20), score int,num int)
go
insert into #tt select 1164 ,'B0422A', 'hux990806', 100, 3
insert into #tt select 1166, 'B0422A', 'hux990806', 100, 3
insert into #tt select 1165, 'B0422A', 'hux990806', 100, 3
insert into #tt select 782, 'B1012A', 'hanwenlong', 85, 5
insert into #tt select 584, 'B1012A', 'hanwenlong', 85, 5
insert into #tt select 785, 'B1012A', 'hanwenlong', 75, 5
insert into #tt select 583, 'B1012A', 'hanwenlong', 75,5
insert into #tt select 582, 'B1012A', 'hanwenlong', 65,5
insert into #tt select 586, 'B1012A', 'hanwenlong', 65, 5 select * from(
select *,rn=(select COUNT(1)+1 from #tt where courseid=t.courseid and userid=t.userid and id<t.id )
from #tt t
) t1
where t1.rn<=5
order by id
--------------
id courseid userid score num rn
582 B1012A hanwenlong 65 5 1
583 B1012A hanwenlong 75 5 2
584 B1012A hanwenlong 85 5 3
586 B1012A hanwenlong 65 5 4
782 B1012A hanwenlong 85 5 5
1164 B0422A hux990806 100 3 1
1165 B0422A hux990806 100 3 2
1166 B0422A hux990806 100 3 3
id courseid userid score num
719 B1028A yinle 100.0 3
1060 B1028A yinle 95.0 3
1059 B1028A yinle 100.0 3
1058 B1028A yinle 90.0 3
721 B1028A yinle 100.0 3
720 B1028A yinle 100.0 3
这个数据有四条score为100分的数据,我使用了你的方法,我怎么都无法取到前3条数据,只能取到四条数据,麻烦在你帮我看看,怎么取到num的记录条数
insert into tb select 719,'B1028A','yiinle',100.0,3
insert into tb select 1060,'B1028A','yiinle',95.0,3
insert into tb select 1059,'B1028A','yiinle',100.0,3
insert into tb select 1058,'B1028A','yiinle',90.0,3
insert into tb select 721,'B1028A','yiinle',100.0,3
insert into tb select 720,'B1028A','yiinle',100.0,3
insert into tb select 782,'B1012A','hanwenlong',85,5
insert into tb select 584,'B1012A','hanwenlong',85,5
insert into tb select 785,'B1012A','hanwenlong',75,5
insert into tb select 583,'B1012A','hanwenlong',75,5
insert into tb select 582,'B1012A','hanwenlong',65,5
insert into tb select 586,'B1012A','hanwenlong',65,5
go
select rn=identity(int,1,1),* into #t from tb order by courseid,userid,id
select id,courseid,userid,score,num from #t a where rn-num<(select min(rn) from #t where courseid=a.courseid and userid=a.userid)
/*
id courseid userid score num
----------- ---------- ---------- --------------------------------------- -----------
582 B1012A hanwenlong 65.0 5
583 B1012A hanwenlong 75.0 5
584 B1012A hanwenlong 85.0 5
586 B1012A hanwenlong 65.0 5
782 B1012A hanwenlong 85.0 5
719 B1028A yiinle 100.0 3
720 B1028A yiinle 100.0 3
721 B1028A yiinle 100.0 3(8 行受影响)*/
go
drop table tb,#t