选择前10行，然后选择5个随机行[关闭](select first 10 rows and then select 5 random row of them [closed])

我想选择前10行，然后选择它们的5个随机行。

i want to select first 10 rows then select 5 random rows of them.

最满意答案

必须在此片段中定义“前10个”并且它是“某事”。 这适用于SQL Server 2000+

select top 5 * from ( select top 10 * from <table> order by something --got to have soemthing here to define "first 10" ) as a order by newid()

编辑：

为什么在派生表中需要ORDER BY

-- large row, same result with and without index/PK CREATE TABLE #foo (bar int /*PRIMARY KEY NONCLUSTERED (bar)*/, eejit char(8000)) --create rows with value 1-10 + some others INSERT #foo (bar) VALUES (1) INSERT #foo (bar) VALUES (10) INSERT #foo (bar) VALUES (20) INSERT #foo (bar) VALUES (2) INSERT #foo (bar) VALUES (5) INSERT #foo (bar) VALUES (45) INSERT #foo (bar) VALUES (99) INSERT #foo (bar) VALUES (3) INSERT #foo (bar) VALUES (9) INSERT #foo (bar) VALUES (7) INSERT #foo (bar) VALUES (6) INSERT #foo (bar) VALUES (4) INSERT #foo (bar) VALUES (8) --create logical fragmentation DELETE #foo WHERE bar IN (1, 3, 5, 7, 9) INSERT #foo (bar) VALUES (1) INSERT #foo (bar) VALUES (3) INSERT #foo (bar) VALUES (5) INSERT #foo (bar) VALUES (7) INSERT #foo (bar) VALUES (9) -- run this a few times, you will see values > 10 -- "first 10" surely means values between 1 and 10? select top 5 * from ( select top 10 * from #foo ) as a order by newid() -- always <= 10 because of ORDER BY select top 5 * from ( select top 10 * from #foo order by bar --got to have soemthing here to define "first 10" ) as a order by newid()

Have to define "first 10" and it's "something" in this snippet. This is for SQL Server 2000+

select top 5 * from ( select top 10 * from <table> order by something --got to have soemthing here to define "first 10" ) as a order by newid()

Edit:

Why you need ORDER BY in the derived table

-- large row, same result with and without index/PK CREATE TABLE #foo (bar int /*PRIMARY KEY NONCLUSTERED (bar)*/, eejit char(8000)) --create rows with value 1-10 + some others INSERT #foo (bar) VALUES (1) INSERT #foo (bar) VALUES (10) INSERT #foo (bar) VALUES (20) INSERT #foo (bar) VALUES (2) INSERT #foo (bar) VALUES (5) INSERT #foo (bar) VALUES (45) INSERT #foo (bar) VALUES (99) INSERT #foo (bar) VALUES (3) INSERT #foo (bar) VALUES (9) INSERT #foo (bar) VALUES (7) INSERT #foo (bar) VALUES (6) INSERT #foo (bar) VALUES (4) INSERT #foo (bar) VALUES (8) --create logical fragmentation DELETE #foo WHERE bar IN (1, 3, 5, 7, 9) INSERT #foo (bar) VALUES (1) INSERT #foo (bar) VALUES (3) INSERT #foo (bar) VALUES (5) INSERT #foo (bar) VALUES (7) INSERT #foo (bar) VALUES (9) -- run this a few times, you will see values > 10 -- "first 10" surely means values between 1 and 10? select top 5 * from ( select top 10 * from #foo ) as a order by newid() -- always <= 10 because of ORDER BY select top 5 * from ( select top 10 * from #foo order by bar --got to have soemthing here to define "first 10" ) as a order by newid()选择前10行，然后选择5个随机行[关闭](select first 10 rows and then select 5 random row of them [closed])

我想选择前10行，然后选择它们的5个随机行。

i want to select first 10 rows then select 5 random rows of them.

最满意答案

必须在此片段中定义“前10个”并且它是“某事”。 这适用于SQL Server 2000+

select top 5 * from ( select top 10 * from <table> order by something --got to have soemthing here to define "first 10" ) as a order by newid()

编辑：

为什么在派生表中需要ORDER BY

-- large row, same result with and without index/PK CREATE TABLE #foo (bar int /*PRIMARY KEY NONCLUSTERED (bar)*/, eejit char(8000)) --create rows with value 1-10 + some others INSERT #foo (bar) VALUES (1) INSERT #foo (bar) VALUES (10) INSERT #foo (bar) VALUES (20) INSERT #foo (bar) VALUES (2) INSERT #foo (bar) VALUES (5) INSERT #foo (bar) VALUES (45) INSERT #foo (bar) VALUES (99) INSERT #foo (bar) VALUES (3) INSERT #foo (bar) VALUES (9) INSERT #foo (bar) VALUES (7) INSERT #foo (bar) VALUES (6) INSERT #foo (bar) VALUES (4) INSERT #foo (bar) VALUES (8) --create logical fragmentation DELETE #foo WHERE bar IN (1, 3, 5, 7, 9) INSERT #foo (bar) VALUES (1) INSERT #foo (bar) VALUES (3) INSERT #foo (bar) VALUES (5) INSERT #foo (bar) VALUES (7) INSERT #foo (bar) VALUES (9) -- run this a few times, you will see values > 10 -- "first 10" surely means values between 1 and 10? select top 5 * from ( select top 10 * from #foo ) as a order by newid() -- always <= 10 because of ORDER BY select top 5 * from ( select top 10 * from #foo order by bar --got to have soemthing here to define "first 10" ) as a order by newid()

Have to define "first 10" and it's "something" in this snippet. This is for SQL Server 2000+

select top 5 * from ( select top 10 * from <table> order by something --got to have soemthing here to define "first 10" ) as a order by newid()

Edit:

Why you need ORDER BY in the derived table

-- large row, same result with and without index/PK CREATE TABLE #foo (bar int /*PRIMARY KEY NONCLUSTERED (bar)*/, eejit char(8000)) --create rows with value 1-10 + some others INSERT #foo (bar) VALUES (1) INSERT #foo (bar) VALUES (10) INSERT #foo (bar) VALUES (20) INSERT #foo (bar) VALUES (2) INSERT #foo (bar) VALUES (5) INSERT #foo (bar) VALUES (45) INSERT #foo (bar) VALUES (99) INSERT #foo (bar) VALUES (3) INSERT #foo (bar) VALUES (9) INSERT #foo (bar) VALUES (7) INSERT #foo (bar) VALUES (6) INSERT #foo (bar) VALUES (4) INSERT #foo (bar) VALUES (8) --create logical fragmentation DELETE #foo WHERE bar IN (1, 3, 5, 7, 9) INSERT #foo (bar) VALUES (1) INSERT #foo (bar) VALUES (3) INSERT #foo (bar) VALUES (5) INSERT #foo (bar) VALUES (7) INSERT #foo (bar) VALUES (9) -- run this a few times, you will see values > 10 -- "first 10" surely means values between 1 and 10? select top 5 * from ( select top 10 * from #foo ) as a order by newid() -- always <= 10 because of ORDER BY select top 5 * from ( select top 10 * from #foo order by bar --got to have soemthing here to define "first 10" ) as a order by newid()