sql - 基于key的随机SELECT查询

Question

是否可以根据 key 随机化 SELECT？

我使用此查询来随机查询:

SELECT * FROM tbl_news ORDER BY NEWID();

是否有类似的函数或方法可以根据键进行随机查询:

SELECT * FROM tbl_news ORDER BY NEWID(15);

5,1,3,2,4

使用相同键:

SELECT * FROM tbl_news ORDER BY NEWID(15);

5,1,3,2,4

使用另一个键:

SELECT * FROM tbl_news ORDER BY NEWID(36);

2,5,1,3,4

Answer 1

您可以使用HASHBYTES ，尽管它在大量行上可能表现不佳(尽管 NEWID() 也没有)，例如

-- CREATE SAMPLE DATA
IF OBJECT_ID(N'tempdb..#tbl_news', 'U') IS NOT NULL DROP TABLE #tbl_news;
CREATE TABLE #tbl_news (NewsID INT IDENTITY);
INSERT #tbl_news DEFAULT VALUES;
INSERT #tbl_news DEFAULT VALUES;
INSERT #tbl_news DEFAULT VALUES;
INSERT #tbl_news DEFAULT VALUES;
INSERT #tbl_news DEFAULT VALUES;

-- DEFINE YOUR SORT KEY
DECLARE @Key INT = 15;

SELECT  *
FROM    #tbl_news
ORDER BY HASHBYTES('MD5', CONVERT(VARCHAR(10), NewsID + @Key));

-- SAME ORDER AS FIRST SELECT TO SHOW SORT IS REPEATABLE
SELECT  *
FROM    #tbl_news
ORDER BY HASHBYTES('MD5', CONVERT(VARCHAR(10), NewsID + @Key));


SET @Key = 36;

-- WITH A NEW KEY SHOW DIFFERENT ORDER
SELECT  *
FROM    #tbl_news
ORDER BY HASHBYTES('MD5', CONVERT(VARCHAR(10), NewsID + @Key));

-- BUT NEW ORDER IS STILL REPEATABLE
SELECT  *
FROM    #tbl_news
ORDER BY HASHBYTES('MD5', CONVERT(VARCHAR(10), NewsID + @Key));