设为首页收藏本站Access中国

Office中国论坛/Access中国论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

返回列表 发新帖
查看: 2802|回复: 2
打印 上一主题 下一主题

[ADO/DAO] 从MSSQL(SQLSERVER)/MYSQL数据库中随机取一条或者多条记录

[复制链接]

点击这里给我发消息

跳转到指定楼层
1#
发表于 2015-10-24 18:34:08 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
从MSSQL(SQLSERVER)/MYSQL数据库中随机取一条或者多条记录
很多人都知道使用rand()函数但是怎麽使用可能不是每个人都知道
摘自桦仔 博文
建立测试表
USE [sss]
GO


CREATE TABLE RANDTEST(ID INT DEFAULT RAND()*100,NAME NVARCHAR(200) DEFAULT 'nihao')
GO


CREATE INDEX IX_RANDTEST_ID ON RANDTEST(ID)
GO




INSERT INTO RANDTEST DEFAULT VALUES
GO 2000


SELECT * FROM RANDTEST

第一种写法:大家会想到ORDER BY NEWID()


SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT TOP 50 [id]  FROM    [dbo].[RANDTEST]
GROUP BY ID
ORDER BY NEWID()
SET STATISTICS TIME OFF
SET STATISTICS IO OFF


这种写法使用到索引扫描,而且每次select出来的结果都是一样的,都是50条记录




第二种写法

SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT TOP 50 [t1].[ID] FROM [dbo].[RANDTEST] t1 JOIN (SELECT RAND()*100 AS nid) t2 ON [t1].[ID]>[t2].[nid]
GROUP BY [t1].[ID]
SET STATISTICS TIME OFF
SET STATISTICS IO OFF


跟t2这个表做比较,而且每次能够达到随机取一条或者N条记录的效果
每次select出来的行数都是不一样的


分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
收藏收藏 分享分享 分享淘帖 订阅订阅

点击这里给我发消息

2#
 楼主| 发表于 2015-10-24 18:34:37 | 只看该作者



比较一下IO和时间
当两种写法select出来的结果条数都是50条的时候,时间和IO都是一样的,如果第二种写法select出来的记录条数不是50条
那么IO肯定比第一种写法要少
--第一种写法  select出来50条记录SQL Server 执行时间:   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。(50 行受影响)表 'RANDTEST'。扫描计数 1,逻辑读取 5 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:   CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。--------------------------------------------------------------------------------第二种写法  select出来37条记录SQL Server 执行时间:   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。(27 行受影响)表 'RANDTEST'。扫描计数 1,逻辑读取 4 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:   CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

总结
如果第一种写法写成下面的样子,那么每次select出来的结果都是一样的,而且不会进行排序,在执行计划里面你看不到排序这个运算符
因为非聚集索引是排好序的,扫描非聚集索引只会得到排好序的结果
SELECT TOP 50 [id  FROM    [dbo.[RANDTESTGROUP BY IDORDER BY RAND()*100


点击这里给我发消息

3#
 楼主| 发表于 2015-10-24 18:34:50 | 只看该作者

综上,想从SQLSERVER数据库中随机取一条或者N条记录时,最好把RAND()生成随机数放在JOIN子查询中以提高效率。
SELECT TOP n [id  FROM    tableGROUP BY IDORDER BY NEWID()
改造成下面这个:
SELECT TOP n   [t1.[ID  FROM table  t1 JOIN (SELECT RAND()*100 AS nid) t2 ON [t1.[ID>[t2.[nidGROUP BY [t1.[ID

就可以享受在SQL中直接取得随机数了,不用再在程序中构造一串随机数去检索了。

MYSQL也是同样的原理
CREATE TABLE `t_innodb_random` (
`id` INT(10) UNSIGNED NOT NULL,
`user` VARCHAR(64) NOT NULL DEFAULT '',
KEY `idx_id` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;




INSERT INTO `t_innodb_random` (`id`, `user`) VALUES('1','lily');
INSERT INTO `t_innodb_random` (`id`, `user`) VALUES('3','tom');
INSERT INTO `t_innodb_random` (`id`, `user`) VALUES('5','fancy');
INSERT INTO `t_innodb_random` (`id`, `user`) VALUES('6','cici');
INSERT INTO `t_innodb_random` (`id`, `user`) VALUES('9','syan');




SELECT * FROM t_innodb_random;




SELECT id FROM t_innodb_random ORDER BY RAND() LIMIT 5;
-- 改造成下面这个:


SELECT id FROM t_innodb_random t1 JOIN (SELECT RAND()*10 AS nid) t2 ON t1.id > t2.nid LIMIT 5;


---------------------------------------------------------------------------------------------


您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|站长邮箱|小黑屋|手机版|Office中国/Access中国 ( 粤ICP备10043721号-1 )  

GMT+8, 2024-11-29 02:12 , Processed in 0.090034 second(s), 26 queries .

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表