从SQL Server表中选择n个随机行 [英] Select n random rows from SQL Server table

问题描述

我有一个SQL Server表，其中包含约50,000行.我想随机选择大约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个随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！