英文:
Randomising query results order, with paging
问题
我有一个需求，需要对查询结果进行随机排序，同时仍然支持分页。显然，我无法对每个 REST API 请求进行真正的随机排序。我在考虑一种种子的随机化方式，然后可以使用它来确定性地重新生成“随机”顺序以供后续调用使用。是否有已经建立的做法来实现这一点，如果没有，是否有任何建议？
我在一个使用 Entity Framework Core 的 .NET 7 WebAPI 中，想要限制从 SQL Server 返回的结果仅为所请求的页面大小。
英文:
I have a requirement to randomise a set of results from a query but still have paging support. Obviously I couldn't do a genuine random sort per hit to the REST API. I'm thinking some kind of randomisation of a seed which could then be used to re-generate the "random" order deterministically for subsequent calls. Is there an established way of doing this and if not, any recommendations?
I'm in a .NET 7 WebAPI using Entity Framework Core and would like to limit the results being returned from SQL Server to just the page size requested.
答案1
得分: 1
请使用HASHBYTES()函数以可重复的方式混合顺序并添加自己的种子值：
CREATE TABLE #Test (ID INT IDENTITY);
GO
-- 插入20行
INSERT #Test
DEFAULT VALUES
GO 20
-- 选择任何种子值
DECLARE @SeedValue INT = 2147483647 -- 测试最大整数值
SELECT HASHBYTES('SHA2_256', CAST(ID ^ @SeedValue AS BINARY(4))) AS SortID, ID FROM #Test ORDER BY SortID, ID;
ID^@SeedValue - 这是二进制异或操作。如果使用简单的数学运算符，可能会发生意外溢出。这可以避免这种情况。
英文:
Just use HASHBYTES() function to mix-up order in a repeatable way and add your own seed value:
CREATE TABLE #Test ( ID INT IDENTITY );
GO
-- Insert 20 rows
INSERT #Test
DEFAULT VALUES
GO 20
-- Pick any seed value
DECLARE @SeedValue INT = 2147483647 -- Testing for Max Int Value
SELECT HASHBYTES ('SHA2_256', CAST( ID^@SeedValue AS BINARY( 4 ))) AS SortID, ID FROM #Test ORDER BY SortID, ID;
ID^@SeedValue - this is binary XOR statement. If you use simple math operators you can accidentally overflow. This avoids this.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论