SQL实现QPackID/QGrpID随机排序且组内QID升序

我有三个主表，当前使用如下SQL语句查询数据：

SELECT
    Q.QPackID, Q.QGrpID, Q.QID
FROM
    dbo.SV_Q AS Q 
LEFT OUTER JOIN
    dbo.SV_Q_Pack AS P ON Q.QPackID = P.QPackID 
LEFT OUTER JOIN
    dbo.SV_QGrp AS G ON Q.QGrpID = G.QGrpID 
LEFT OUTER JOIN
    dbo.SV_QM AS M ON Q.QmID = M.QmID
WHERE
    Q.QID IN (SELECT QID FROM MyTable WHERE MyID = 123); 

查询得到的原始数据如下：

数据层级关系 #

• QID 隶属于 QGrpID
• QGrpID 隶属于 QPackID

排序规则要求 #

• QPackID 全局随机排序，但同一QPackID下的记录保持连续
• 同一QPackID内的QGrpID随机排序，但同一QGrpID下的记录保持连续
• 同一QGrpID内的QID保持升序

期望排序结果 #

解决方案 #

要实现这种层级随机排序，可以通过生成分组随机排序键的方式，确保同一层级内的记录连续，同时上层分组随机排列。以下是两种可行的实现方式：

方式1：直接在ORDER BY中生成随机键

SELECT
    Q.QPackID, Q.QGrpID, Q.QID
FROM
    dbo.SV_Q AS Q 
LEFT OUTER JOIN
    dbo.SV_Q_Pack AS P ON Q.QPackID = P.QPackID 
LEFT OUTER JOIN
    dbo.SV_QGrp AS G ON Q.QGrpID = G.QGrpID 
LEFT OUTER JOIN
    dbo.SV_QM AS M ON Q.QmID = M.QmID
WHERE
    Q.QID IN (SELECT QID FROM MyTable WHERE MyID = 123)
ORDER BY
    -- 为每个QPackID生成唯一随机键，保证同组连续
    (SELECT NEWID() FROM dbo.SV_Q WHERE QPackID = Q.QPackID LIMIT 1),
    -- 为同一QPackID下的每个QGrpID生成唯一随机键
    (SELECT NEWID() FROM dbo.SV_Q WHERE QPackID = Q.QPackID AND QGrpID = Q.QGrpID LIMIT 1),
    -- 同一QGrpID内QID升序
    Q.QID ASC;

方式2：用CTE预计算随机键（性能更优）

WITH BaseData AS (
    SELECT
        Q.QPackID, Q.QGrpID, Q.QID
    FROM
        dbo.SV_Q AS Q 
    LEFT OUTER JOIN
        dbo.SV_Q_Pack AS P ON Q.QPackID = P.QPackID 
    LEFT OUTER JOIN
        dbo.SV_QGrp AS G ON Q.QGrpID = G.QGrpID 
    LEFT OUTER JOIN
        dbo.SV_QM AS M ON Q.QmID = M.QmID
    WHERE
        Q.QID IN (SELECT QID FROM MyTable WHERE MyID = 123)
),
PackRandom AS (
    SELECT DISTINCT QPackID, NEWID() AS PackKey
    FROM BaseData
),
GroupRandom AS (
    SELECT DISTINCT QPackID, QGrpID, NEWID() AS GroupKey
    FROM BaseData
)
SELECT
    bd.QPackID, bd.QGrpID, bd.QID
FROM BaseData bd
JOIN PackRandom pr ON bd.QPackID = pr.QPackID
JOIN GroupRandom gr ON bd.QPackID = gr.QPackID AND bd.QGrpID = gr.QGrpID
ORDER BY
    pr.PackKey,
    gr.GroupKey,
    bd.QID ASC;

核心逻辑说明

• 使用NEWID()生成随机GUID作为排序键，同一分组（QPackID/QGrpID）的记录会关联到同一个随机键，因此分组内记录保持连续，分组间则随机排序。
• 第二种方式通过CTE预先计算所有分组的随机键，避免重复计算，在数据量较大时性能更稳定。

内容的提问来源于stack exchange，提问作者Hanericka