TOP 15 WITH TIES是什么意思

[k10509806]

查询如下课程成绩第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”这些又是怎么来的？

[SQL77]

WITH TIES

指定从基本结果集中返回附加的行，这些行包含与出现在 TOP n (PERCENT) 行最后的 ORDER BY 列中的值相同的值。如果指定了 ORDER BY 子句，则只能指定 TOP ...WITH TIES。

[vivian_lanlan]

比如现在有一些数据.
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都取出来.

[bancxc]

看看就明白了

select top 3 WITH TIES n
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 行受影响)

[bancxc]

看看就明白了

select top 3 n
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 行受影响)

[k10509806]

[Quote=引用 1 楼 sql77 的回复:]
SQL codeWITH TIES

指定从基本结果集中返回附加的行，这些行包含与出现在TOP n (PERCENT) 行最后的ORDERBY 列中的值相同的值。如果指定了ORDERBY 子句，则只能指定TOP ...WITH TIES。
[/Quote]
还有为什么是top 15啊？ 而且那些“k1”这些又是怎么来的？

[华夏小卒]

--> 测试数据: @tb
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 4

select 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 行受影响)

[vivian_lanlan]

[Quote=引用 5 楼 k10509806 的回复:]
引用 1 楼 sql77 的回复:
SQL codeWITH TIES

指定从基本结果集中返回附加的行，这些行包含与出现在TOP n (PERCENT) 行最后的ORDERBY 列中的值相同的值。如果指定了ORDERBY 子句，则只能指定TOP ...WITH TIES。

还有为什么是top 15啊？ 而且那些“k1”这些又是怎么来的？
[/Quote]

你想top 15就top 15..你想top 50就top 50

[k10509806]

哦，原来是这个意思，那它怎么实现第3名到第6名的？

[--小F--]

取n到m行

1. 
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

[--小F--]

貌似第3-6名与with ties没什么关系

[fcuandy]

t-sql top x with ties = jetsql top x

[k10509806]

[Quote=引用 11 楼 fcuandy 的回复:]
t-sql top x with ties = jetsql top x
[/Quote]
看不懂