sql - 每种列类型的 SQL Server 随机行

Question

我正在尝试为他们的 playerRole 列获取 3 个不同的玩家。我在用

SELECT TOP 1 * FROM FantasyPlayers WHERE playerRole = 1 ORDER BY NEWID()

查询获得 playerRole = 1 的随机行

但是我想得到

SELECT TOP 1 * FROM FantasyPlayers WHERE playerRole = 1 ORDER BY NEWID()

SELECT TOP 1 * FROM FantasyPlayers WHERE playerRole = 2 ORDER BY NEWID()

SELECT TOP 1 * FROM FantasyPlayers WHERE playerRole = 3 ORDER BY NEWID()

3 个具有 3 个不同角色的随机玩家，这意味着每个 playerRole 有 1 个随机玩家。

我尝试了由于 NEWID() 而没有返回任何内容的 Union

任何帮助表示赞赏

谢谢

标签： sqlsql-server

Answer 1

也许...

WITH CTE AS(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY playerRole
                              ORDER BY NEWID()) AS RN
    FROM FantasyPlayers)
SELECT *
FROM CTE
WHERE RN = 1;