gpt4 book ai didi

sql - 选择前 10 行,然后随机选择其中 5 行

转载 作者:行者123 更新时间:2023-12-02 01:32:23 29 4
gpt4 key购买 nike

我想选择前 10 行,然后随机选择其中 5 行。

最佳答案

必须定义“前 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()

关于sql - 选择前 10 行,然后随机选择其中 5 行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4365829/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com