php - ORDER BY rand() 并使用 group_id 进行分组

Question

我想按 group_id 排序以随机顺序选择行:

id  group_id
1   1
2   1
3   2
4   2
5   3
6   4

我希望它们像下面这样排序或随机但仍分组:

id  group_id
5   3
3   2
4   2
1   1
2   1
6   4

下面将对它们进行排序，但我希望排序是随机的。如何实现？

SELECT * FROM table ORDER BY group_id DESC

下面是我为实际应用程序计算的示例查询。

SELECT * FROM ( SELECT requests.*, accounts.username, accounts.password
FROM requests LEFT JOIN accounts ON requests.acc_id = accounts.id
WHERE requests.status NOT IN(1) AND accounts.status=1 ORDER BY RAND()
)  AS subquery GROUP BY acc_id 

Answer 1

您可以尝试按随机数排序，种子取决于 group_id，但具有随机因子。像这样:

SET @seed = 100*RAND();
SELECT * FROM table ORDER BY RAND(group_id*@seed);