我想了解 with ties 的作用,我尝试写了下面的sql语句来测试,但是当我执行最后两个语句的时候,发现都是一样的结果。
我想问下大家with ties 的具体作用,以及我这个例子应该怎么改才能体现出 with ties 的作用。谢谢你们的回复。
create table stuinfo(
id int primary key,
sore int
)insert into stuinfo select 1,70
union select 2,85
union select 3,80
union select 4,80
union select 5,90
union select 6,50
union select 7,60
union select 8,70
--执行比较
select top 3 * from stuinfo order by id asc
select top 3 with ties * from stuinfo order by id asc
我想问下大家with ties 的具体作用,以及我这个例子应该怎么改才能体现出 with ties 的作用。谢谢你们的回复。
create table stuinfo(
id int primary key,
sore int
)insert into stuinfo select 1,70
union select 2,85
union select 3,80
union select 4,80
union select 5,90
union select 6,50
union select 7,60
union select 8,70
--执行比较
select top 3 * from stuinfo order by id asc
select top 3 with ties * from stuinfo order by id asc
select top 3 with ties * from stuinfo order by id asc
表示按id排序取前三行数据,如果id有重复的也一起显示出来..
insert into tb
select 60 union all select 85 union all select 70 union all
select 90 union all select 85 union all select 100select top(3) * from tb order by score
/*
1 60
3 70
2 85
*/select top(3) with ties * from tb order by score
/*
1 60
3 70
2 85
5 85
*/
这句的意思是按id 从小到大的顺序取stuinfo 的前3条记录,如果第3条的id 重复,那么这个id号的记录都取出来,这样取出来的记录就不止3条了
go
create table stuinfo(
id int ,
sore int
)insert into stuinfo select 1,70
union select 2,85
union select 3,80
union select 3,40
union select 5,90
union select 6,50
union select 7,60
union select 8,70
--执行比较
select top 3 * from stuinfo order by id asc
/*
1 70
2 85
3 80
*/
select top 3 with ties * from stuinfo order by id asc
/*
1 70
2 85
3 40
3 80
*/
--如果分数相同就是三条记录了
select top 3 * from stuinfo order by sore desc ------注意这条我改了id--》sore
select top 3 with ties * from stuinfo order by sore desc ------注意这条我改了id--》sore
/*
id sore
----------- -----------
5 90
2 85
4 80id sore
----------- -----------
5 90
2 85
3 80
4 80
*/
create table stuinfo(
id int primary key,
sore int
)insert into stuinfo select 1,70
union select 2,85
union select 3,80
union select 4,80
union select 5,80
union select 6,80
union select 7,90
union select 8,50
union select 9,60
union select 10,70
--执行比较
select top (3) * from stuinfo order by sore
select top (3) with ties * from stuinfo order by soredrop table stuinfo
/*
(10 行受影响)
id sore
----------- -----------
8 50
9 60
1 70(3 行受影响)id sore
----------- -----------
8 50
9 60
10 70
1 70(4 行受影响)*/
if object_id('stuinfo') is not null drop table stuinfo
go
create table stuinfo(
id int ,
sore int
)insert into stuinfo select 2,70
union select 2,80
union select 2,40
union select 2,80
union select 5,90
union select 6,50
union select 7,60
union select 8,70
--执行比较
select top 3 * from stuinfo order by id asc
/*
2 80
2 70
2 40
*/
select top 3 with ties * from stuinfo order by id asc
/*
2 40
2 70
2 80
*/
--看,排序不一样
drop table stuinfo
go
create table stuinfo(
id int,
sore int
)insert into stuinfo select 1,70
union select 2,85
union select 3,80
union select 3,100
union select 3,90
union select 6,50
union select 7,60
union select 8,70
--执行比较
select top 3 * from stuinfo order by id asc
select top 3 with ties * from stuinfo order by id asc这样你就看出区别了
若再添加一条记录insert into stuinfo select 2,80
则
select top 3 * from stuinfo order by id asc
select top 3 with ties * from stuinfo order by id asc
执行后的结果却是一样的
第一条执行
1 70
2 85
2 80
第二条执行
1 70
2 85
2 80这是为什么呢?我有些不明白