来谈谈SQL数据库中"简单的"SELECT TOP—可能有你从未注意到的细节

作者:雨辰 发布于:2011-1-10 13:59 Monday 分类:MySql

首先从博客园的Jerome Wong网友说起,,他提出了一个这样的问题,本人写了好几年SQL语句了,从来没注意到这件事情。 

  例如:

  数据表如下:

ID  EMPNO  NAME  AGE  
1   26929   Jerome   28
2   28394   Quince  27
3   20983   Green   30
4   27189   Mike    30
5   23167   Arishy   30
6   26371   Yager   29

  我写了SQL语句想取得第3、4笔数据,测试分页玩的。

  select  top 2 * from (select top 4 * from Member ) m  order by m.RowID desc

  我执行中间那一段子查询:select top 4 * from Member

  取得的是:

1   26929   Jerome   28
2   28394   Quince  27
3   20983   Green   30
4   27189   Mike    30

  但是整个SQL语句的结果却是:

5   23167   Arishy   30
6   26371   Yager   29

  真的不知道到底怎么会出现这种情况,请高手指教。

  其实不管你是新手还是高手在写程序当中经常会碰到类似这样的细节问题,下面我就对Jerome Wong网友所提出的问题针对select top做出一系列的分析(在这里要感谢Jerome Wong网友提出的这个问题)

  准备工作:

ifobject_id('zhuisuo')isnotnull
droptable zhuisuo
go
createtable zhuisuo
(
id intnull,
name varchar(20) null
)
insertinto zhuisuo values(1,'追索1')
insertinto zhuisuo values(2,'追索2')
insertinto zhuisuo values(3,'追索3')
insertinto zhuisuo values(4,'追索4')
insertinto zhuisuo values(5,'追索5')
insertinto zhuisuo values(6,'追索6')
insertinto zhuisuo values(7,'追索7')
insertinto zhuisuo values(8,'追索8')
insertinto zhuisuo values(9,'追索9')
insertinto zhuisuo values(10,'追索10')
go

  下面我们来简单写两句Select语句:

selecttop2*from (selecttop4*from zhuisuo) m orderby m.id desc
selecttop2*from (selecttop4*from zhuisuo orderby id asc) m orderby m.id desc

  执行结果大家会发现:

  平常很多人会认为这两条语句执行的结果会一样,怎么会这样呢?

  从这个查询计划中大家可以清楚的看到:

  第一种扫描完zhuisuo表后先降序(top N Sort)然后在4行范围中取前2行。

  第二种扫描完zhuisuo表后先升序取4行(top N Sort)然后再把这4行降序取2行(top N Sort)。

  在这里就不得不简单的说说SQL语句中出现的表子查询了,表子查询,而出现在from子句中的表我们称为派生表。派生表是虚拟的,未被物理具体化,也就是说当编译的时候。

  如:(select top 2 * from (select top 4 * from zhuisuo) m order by m.id desc )

  外部查询和内部查询会被合并,并生成一个计划,这时再看看上面的执行计划就一目了然了,(注意事项:在派生表里面一般不允许使用order by除非指定了top,也就是说select top 2 * from (select * from zhuisuo order by id asc) m order by m.id desc这句语句是不能执行的)。

  接下来我再举例关于top需要注意的细节:

  1、使用top返回随机行,很多人会想到用RAND函数从而得到这样一个语句。

  select top 4 id,name from zhuisuo order by rand();

  经过多次查询后,你会失望的发现它没有返回随机行,这是因为每个查询只调用它一次而不是每行调用它一次

  这时我们可以把RAND改为Newid:

  select top 4 id,name from zhuisuo order by newid();

  这时就会得到你想要的结果了,在这里我们可以意识到NEWID具有更好的分布特性:

  2、注意insert中使用top。

  insert top (4) into zhuisuo select * from zhuisuo order by id desc

  很多网友会解释为把zhuisuo表中最后4条插入表,但执行完毕后又会让你失望了,插入的是最前面的4条,正确的倒叙插入top方法应该是:

  insert into zhuisuo select  top (4) * from zhuisuo order by id desc

  这两条语句又有什么区别:

  其实第上面那条语句更本就没有排序(Top N Sort)。

  3、有时我想删除数据表里面时间最近的5条数据怎么办。

  delete 和update使用top的时候不能使用order by 

  现在我们可以这样来解决:

delete zhuisuo
where id in (selecttop(5) id from zhuisuo orderby id desc) 

update zhuisuo
set name='追索'+name
where id in (selecttop(5) id from zhuisuo orderby id desc) 

  这是变相实现Top N sort更新或删除数据 但这不是最优的方法因为这还要根具id去匹配,这时我们可以使用这种方法:

with cte_del as
(selecttop(5) *from zhuisuo orderby id desc)
deletefrom cte_del

with cte_del as
(selecttop(5) *from zhuisuo orderby id desc)
update cte_del set name='追索'+name

  4、top除了这些还有更多的用处,就比如之前我使用Top N sort 加 apply回答过一个网友的问题。

  如何查询某用户近一个月内正确率大于60%的阅读记录,每天只显示符合条件正确率最高的那个:

  在这里我只稍微提一下关于apply 也有很多有意思的细节今后有时间我会用随笔形式写出来,最后附上一张关于我用序号表示逻辑查询处理的步骤 :

  文章以实用为主,今天就写到这里,你学到了吗?

标签: MySql

评论:

WillieWomb
2016-06-24 10:49
http://www.aeracommunity.com/p-aid-9745.html
http://www.aeracommunity.com/p-laid-9634.html
http://www.aeracommunity.com/p-laid-5278.html
http://www.aeracommunity.com/p-aid-4444.html
http://www.aeracommunity.com/p-aid-1542.html
http://www.aeracommunity.com/p-laid-10899.html
http://www.aeracommunity.com/p-laid-5473.html
http://www.aeracommunity.com/p-laid-774.html
http://www.aeracommunity.com/p-laid-1722.html
http://www.aeracommunity.com/p-laid-4510.html
http://www.aeracommunity.com/p-laid-10711.html
http://www.aeracommunity.com/p-aid-7652.html
http://www.aeracommunity.com/p-laid-5103.html
http://www.aeracommunity.com/p-laid-6563.html
http://www.aeracommunity.com/p-aid-8296.html

Doc Johnson Ballsy Black Super Dildo, 9 Inches
Laser Engraved BDSM Trooper Spanking Paddle in Maple
Puzzle Cube, Haip搴?3x3x3 Carbon Fiber Sticker Speed Smooth Magic Cube Puzzle Cube Black
Eden Toys Tiger paddle - Faux fur Paddle
ERGOWEAR UNDERWEAR Bikini 0217
WillieWomb
2016-06-19 09:03
http://www.aeracommunity.com/p-laid-9492.html
http://www.aeracommunity.com/p-laid-3226.html
http://www.aeracommunity.com/p-aid-4365.html
http://www.aeracommunity.com/p-laid-9760.html
http://www.aeracommunity.com/p-aid-10352.html
http://www.aeracommunity.com/p-laid-5026.html
http://www.aeracommunity.com/p-aid-9384.html
http://www.aeracommunity.com/p-laid-2362.html
http://www.aeracommunity.com/p-laid-1491.html
http://www.aeracommunity.com/p-laid-6940.html
http://www.aeracommunity.com/p-aid-9439.html
http://www.aeracommunity.com/p-aid-8969.html
http://www.aeracommunity.com/p-aid-4929.html
http://www.aeracommunity.com/p-laid-10847.html
http://www.aeracommunity.com/p-laid-7590.html

Purple-Licious Slumber Bag
Three Motor Parts Vibrating Anal Butt Beads Silicone
Breaking Waves Girls' Island Stripe Tankini Swimsuit
Tracy's Dog閳?60 Degree Rotation Female Masturbation
Pull the Tab: Colors (DK Pull the Tab)

发表评论:

雨辰 joyimp|@2011-2017 京ICP备16030765号