最近在看SQL Cookbook ,在第一章第十小结,提出了一个有趣的问题:
从表中随机返回N条记录,例如一张100条记录的表,随机返回其中的5条记录。
书中给出了DB2、MySQL、PostgreSQL、Oracle以及SQL Server数据库管理系统的解决方案,本人很懒,所以此次只摘出MySQL、Oracle及SQL Server解决方案的代码和大家讨论。
MySQL使用内置的RAND函数、LIMIT和ORDER BY子句,代码如下:
SELECT *
FROM Test
ORDER BY RAND() LIMIT 5
Oracle的代码相对来说比较复杂,使用到了子查询,代码如下:
SELECT *
FROM (
SELECT *
FROM Test
ORDER BY DBMS_RANDOM.VALUE()
)
WHERE ROWNUM <= 5
SQL Server使用内置的NEWID函数,TOP及ORDER BY子句,相对比较简洁,代码如下:
SELECT TOP 5 *
FROM Test
ORDER BY NEWID()
以上代码都是出自SQL Cookbook,为了便于理解,我做了小的改动,将列名使用通配符“*”代替,一律使用Test表,你可以替换成其他任意的表,但是这张表不能是空表或者行数小于5行,否则看不出效果。
我的问题是,这是怎么实现的呢?
我就根据SQL Server的解决方案谈谈自己的理解,可以看到核心的语句时ORDER BY子句,书中说在ORDER BY子句中使用函数与数字常量不同,使用数字常量时表示根据SELECT列表中相应位置的列(字段)来排序,相当于列号。使用函数时则按函数在每一行计算的结果排序。
书中大致原理说明白了,可是不觉得依旧费解吗?
我就简单的理解,相当于增加了一个计算列,假如有如下数据:
列1 列2 列3
a aa aaa
b bb bbb
c cc ccc
d dd ddd
e ee eee
f ff fff
g gg ggg
h hh hhh
执行解决方案中的SQL Server的代码后相当于这样:
列1 列2 列3 NEWID()
a aa aaa 随机数
b bb bbb 随机数
c cc ccc 随机数
d dd ddd 随机数
e ee eee 随机数
f ff fff 随机数
g gg ggg 随机数
h hh hhh 随机数
然后根据NEWID()所代表的列来排序,达到随机返回的效果。
最后说说NEWID()这个函数,NEWID()函数返回一个UUID(微软的实现叫做GUID)形式如下:
代码:
SELECT NEWID()
结果:
无名列
8D902998-582E-4515-9AF8-C0895D36F86E
实际上他是一个uniqueidentifier类型的值。