查询如下课程成绩第3名到第6名的学生成绩单 [学生学号] [学生姓名] 企业管理,马克思,UML,数据库,总分 SELECT DISTINCT top 3 SC.Sno As 学生学号, Student.Sname AS 学生姓名, T1.score AS 企业管理, T2.score AS 马克思, T3.score AS UML, T4.score AS 数据库, ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分 FROM Student, SC LEFT JOIN SC AS T1 ON SC.Sno = T1.Sno AND T1.Cno = '001' LEFT JOIN SC AS T2 ON SC.Sno = T2.Sno AND T2.Cno = '002' LEFT JOIN SC AS T3 ON SC.Sno = T3.Sno AND T3.Cno = '003' LEFT JOIN SC AS T4 ON SC.Sno = T4.Sno AND T4.Cno = '004' WHERE student.Sno=SC.Sno AND ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) NOT IN (SELECT DISTINCT TOP 15 WITH TIES ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) FROM sc LEFT JOIN sc AS T1 ON sc.Sno = T1.Sno AND T1.Cno = 'k1' LEFT JOIN sc AS T2 ON sc.Sno = T2.Sno AND T2.Cno = 'k2' LEFT JOIN sc AS T3 ON sc.Sno = T3.Sno AND T3.Cno = 'k3' LEFT JOIN sc AS T4 ON sc.Sno = T4.Sno AND T4.Cno = 'k4' ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC); 子查询中 TOP 15 WITH TIES 是什么意思啊?还有“k1”这些又是怎么来的?
col
1
3
3
2如果此时,我要取 top 3 col order by col
那结果就是1 2 3 只取一个3
如果在top里加上with ties时,
如果top N中的数还有相等的.也取出来.所以如果在top 3 中也加上with ties的话,那就取1 2 3 3
两个3都取出来.
from (
select n=1 union all
select n=4 union all
select n=3 union all
select n=2 union all
select n=4 union all
select n=6 union all
select n=6 union all
select n=8 union all
select n=9
) t
order by n desc n
-----------
9
8
6
6(4 行受影响)
from (
select n=1 union all
select n=4 union all
select n=3 union all
select n=2 union all
select n=4 union all
select n=6 union all
select n=6 union all
select n=8 union all
select n=9
) t
order by n desc n
-----------
9
8
6(3 行受影响)
declare @tb table (id int)
insert into @tb
select 1 union all
select 2 union all
select 2 union all
select 3 union all
select 3 union all
select 4select top 4 with ties * from @tb
order by id
/*
id
-----------
1
2
2
3
3 ----这个3也出来(5 行受影响)
*/
select top 4 * from @tb
order by id id
-----------
1
2
2
3(4 行受影响)
你想top 15就top 15..你想top 50就top 50
select top m * from tablename where id not in (select top n id from tablename order by id asc/*|desc*/) 2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入到临时表
set rowcount n --只取n条结果
select * from 表变量 order by columnname desc 3.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
4.如果tablename里没有其他identity列,那么:
先生成一个序列,存储在一临时表中.
select identity(int) id0,* into #temp from tablename 取n到m条的语句为:
select * from #temp where id0 > =n and id0 <= m 如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行:
exec sp_dboption 你的DB名字,'select into/bulkcopy',true
5.如果表里有identity属性,那么简单:
select * from tablename where identity_col between n and m 6.SQL2005开始.可以使用row_number() over()生成行号
;with cte as
(
select id0=row_number() over(order by id),* from tablename
)
select * from cte where id0 between n to m