TOP 15 WITH TIES是什么意思

2009-12-16 07:23:23 1.2k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

openEuler 22.03 安装及配置Gitlab容器在 openEuler 22.03 上安装及配置Gitlab容器阅读详情

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

...全文

单相变换器的二次纹波分析 1. 单相PWM整流器的二次纹波产生原因单相变换器的二次纹波产生的原因是功率含有二次脉动分量。设电网电压 us=Vmsin(wt)，电网电流IL=Imsin(wt−θ)I_L=I_msin(wt-\theta)IL=Imsin(wt−θ)。其中w是电网电压披绿，θ\thetaθ是电流之后电压相位。保证输入功率和输出功率相等，就要保证两者的直流量和交流量对应相等。 uDu_DuD为 PWM 整流器直流电容电压平均值，IoI_oIo 为负载电流平均值，uDC^\widehat {u_{DC} 阅读详情

k10509806

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

k10509806 2009-12-17

打赏
举报

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

fcuandy 2009-12-16

打赏
举报

t-sql top x with ties = jetsql top x

--小F-- 2009-12-16

打赏
举报

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

--小F-- 2009-12-16

打赏
举报

取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

k10509806 2009-12-16

打赏
举报

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

vivian_lanlan 2009-12-16

打赏
举报

[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

华夏小卒 2009-12-16

打赏
举报

--> 测试数据: @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 行受影响)

k10509806 2009-12-16

打赏
举报

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

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

bancxc 2009-12-16

打赏
举报

看看就明白了



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

bancxc 2009-12-16

打赏
举报

看看就明白了



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

vivian_lanlan 2009-12-16

打赏
举报

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

SQL77 2009-12-16

打赏
举报

WITH TIES



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

TOP 15 WITH TIES是什么意思

相关推荐