由派生表引发的思考
0
悬赏园豆：5
[已解决问题]
解决于 2014-01-10 14:19
if object_id('temptable')is not null
drop table temptable
go
create table temptable(id int identity(1,1) ,tempid int null,name varchar(20) null)
insert into temptable values(1,'测试1')
insert into temptable values(2,'测试2')
insert into temptable values(3,'测试3')
insert into temptable values(4,'测试4')
insert into temptable values(5,'测试5')
insert into temptable values(6,'测试6')
insert into temptable values(7,'测试7')
insert into temptable values(8,'测试8')
insert into temptable values(9,'测试9')
insert into temptable values(10,'测试10')
go
--这两个查询返回不相同的结果集
select top 2 * from (select top 4 * from temptable) m order by m.id desc
select top 2 * from (select top 4 * from temptable order by id asc) m order by m.id desc
/*引用博客园 追索：
"派生表是虚拟的，未被物理具体化，也就是说当编译的时候；外部查询和内部查询会被合并，并生成一个计划"*/
--得到如下两个查询等效
select top 2 * from (select top 4 * from temptable) m order by m.id desc
select top 2 * from (select top 4 * from temptable m order by m.id desc)a
/*
引用《SQLServer2008技术内幕：T-SQL查询》
1.除非对查询进行排序，否则 TOP 具有不确定性；
2.select top(3) from temptable 有可能返回不容的结果集；*/
问题：1。这个问题是否是因为派生表的原因还是TOP不确定性导致的？
2.对 "select top(3) from temptable 有可能返回不同的结果集；"
查询会按照物理插入顺序去取top3条怎么会返回不同的结果集呢？
最佳答案
0
你好,
1.我发现你建立的测试表并未包含聚集索引,固在此我自行创了一个聚集索引
ALTER TABLE temptable
ADD CONSTRAINT PK_temptable_id
PRIMARY KEY CLUSTERED
(id)
2.关于你的问题1的回复
我看到你的子查询没有使用order by而外层则用了.所以可以把
SELECT TOP 2 * FROM (
SELECT TOP 4 * FROM temptable
) m
ORDER BY m.id DESC
--看作
SELECT TOP 2 * FROM (
SELECT TOP 4 * FROM temptable
ORDER BY id DESC --外层的order by对内层起了作用.所以返回10, 9, 8, 7
) m
ORDER BY m.id DESC
所以你看到返回10, 9并不出奇
从而验证了
引用《SQLServer2008技术内幕：T-SQL查询》
1.除非对查询进行排序，否则 TOP 具有不确定性
3.关于你的问题2的回复
你的select top(3) from temptable应该是
select top(3) * from temptable吧
而
SELECT TOP 2 * FROM (
SELECT TOP 4 * FROM temptable
--order by m.id desc --还是那句外层order by对子查询产生了影响
) m
ORDER BY m.id DESC
SELECT TOP 2 * FROM (
SELECT TOP 4 * FROM temptable m
ORDER BY m.id DESC --10, 9, 8, 7
) a
--所以top 2返回10, 9
若要深究,请学习执行计划的相关知识!
收获园豆：5
