问题描述
我有一个包含大约 50,000 行的 SQL Server 表.我想随机选择大约 5,000 行.我想到了一个复杂的方法,创建一个带有随机数"列的临时表,将我的表复制到其中,循环遍历临时表并使用 RAND()
更新每一行,然后从该表中选择随机数列<0.1.我正在寻找一种更简单的方法来做到这一点,如果可能的话,在一个语句中.
I've got a SQL Server table with about 50,000 rows in it. I want to select about 5,000 of those rows at random. I've thought of a complicated way, creating a temp table with a "random number" column, copying my table into that, looping through the temp table and updating each row with RAND()
, and then selecting from that table where the random number column < 0.1. I'm looking for a simpler way to do it, in a single statement if possible.
本文建议使用NEWID()
函数.这看起来很有希望,但我不知道如何可靠地选择一定百分比的行.
This article suggest using the NEWID()
function. That looks promising, but I can't see how I could reliably select a certain percentage of rows.
以前有人这样做过吗?有什么想法吗?
Anybody ever do this before? Any ideas?
推荐答案
select top 10 percent * from [yourtable] order by newid()
针对关于大表的纯垃圾"评论:您可以这样做以提高性能.
In response to the "pure trash" comment concerning large tables: you could do it like this to improve performance.
select * from [yourtable] where [yourPk] in
(select top 10 percent [yourPk] from [yourtable] order by newid())
这样做的成本将是值的键扫描加上连接成本,这在选择百分比很小的大表上应该是合理的.
The cost of this will be the key scan of values plus the join cost, which on a large table with a small percentage selection should be reasonable.
这篇关于从 SQL Server 表中随机选择 n 行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!