MySql: 生成随机行作为子查询

Question

我在子查询中遇到了一个相关性问题。

我有两个表：
- table1：包含"groups"，每个group拥有一个groupid、一个groupname和一个categoryid
- table2：告诉我们哪些人是哪个group的成员(具有字段：userid、groupid)

我想要从数据库获取以下信息：
特定"category"的所有groups，对于每个group：
- groupid、groupname
- 随机选择4个成员

我参考了MySQL select 10 random rows from 600K rows fast文中的方法来为每个group生成4个随机成员。

如果我单独运行这个查询，它可以正常工作。

但如果我试图将我的子查询嵌入到“总查询”中：

SELECT 
g.groupid, g.groupname, 
(
SELECT GROUP_CONCAT(table2.userid SEPARATOR ",") 
    FROM table2, (
        SELECT userid AS uid
        FROM table2 
        WHERE table2.groupid = g.groupid 
        ORDER BY RAND( ) 
        LIMIT 4 
    ) tmp 
WHERE table2.userid = tmp.uid
) AS randomusers 
FROM table1 AS g WHERE g.categoryid = ? 

我遇到了一个错误："Unknown column 'g.groupid' in 'where clause'"

我尝试将子查询传递给 LEFT JOIN 但是我无法正确地实现，我的每次尝试都不成功。

有什么帮助吗？谢谢 :)

- SebT

Answer 1

如果我理解正确，你的查询看起来过于复杂。

SELECT t1.groupid, 
       t1.groupname, 
       (SELECT GROUP_CONCAT(table2.userid) FROM table2 WHERE table2.userid = t1.userid ORDER BY RAND() LIMIT 4) AS `users`
FROM table1 AS t1

尚未测试，但此查询应返回每个组的前四个随机元素

- dkasipovic

---

谢谢你的回答！我同意我的查询非常复杂，但是直接使用“ORDER BY RAND()”会非常慢。为了优化这种类型的查询，强烈建议使用子查询，只对“id”进行排序。（请参见我发布的链接或http://theoryapp.com/select-random-records-in-mysql/） - SebT

确实，我的查询将从table2表中拉取所有记录，按照随机顺序进行排序，然后取前4个。按rand排序总是更慢，但如果你想在表中使用它，可以从group_concat中移除它，然后将“LEFT JOIN table2”更改为“LEFT JOIN (SELECT ... ORDER BY RAND() LIMIT 4) table2”。 - dkasipovic

实际上，我认为第二个嵌套子查询"SELECT userid AS uid FROM table2 WHERE table2.groupid = g.groupid ORDER BY RAND( ) LIMIT 4" 是用来仅对具有较小大小的ID进行排序，而不是对整个表进行排序... - SebT

是的，你说得对，LIMIT只限制结果数量（当使用GROUP_CONCAT时始终为1）。你有没有考虑过在后处理中获取用户的方法？例如，获取所有组，然后对于每个组获取4个随机用户？这可行吗？ - dkasipovic

当然可以...但是我真的想避免这样做 :) 你有关于如何在左连接中获得一个2级深度子查询（带相关性）的想法吗？ - SebT