mysql - 将这三个 SELECT 语句组合起来是否可行且谨慎？

Question

我有以下 MySQL 表:

LEVEL(lid)    USERS(uid,name,l_id)   USERS_G(u_id,r_id,flag)   REQUESTS(rid,status)
---------------------------------------------------------------------------------
  1              1  John   1         1  96   1                  96    0
  2              2  Peter  1         1  97   1                  97    0
  3              3  Helen  2         2  97   0                  98    0 
                                     3  97   0                  99    1 
                                     2  98   1
                                     1  98   0
                                     3  98   0

LEVEL 到 USERS 1:N，USERS 到 USERS_G 1:N，REQUESTS 到 USERS_G 1:N。

我想从具有特定状态的请求中选择那些记录，同时这些请求记录的用户持有者具有特定级别。如果 USERS_G 中的相应标志等于 1，则认为用户是请求的持有者。这很容易实现，如下所示:

SELECT lid,rid,name FROM REQUESTS JOIN USERS_G 
        ON REQUESTS.rid = USERS_G.r_id AND flag=1 AND status='0'
        JOIN USERS 
        ON USERS_G.u_id=USERS.uid
        JOIN LEVEL 
        ON LEVEL.lid=USERS.l_id AND LEVEL.lid='1';   

以上查询结果为:

1    96    John
1    97    John
1    98    Peter

到目前为止，还不错。此外，我想找到与之前的任何请求相关联的记录数。换句话说:

SELECT COUNT(*) FROM REQUESTS JOIN USERS_G 
        ON REQUESTS.rid = USERS_G.r_id
        WHERE REQUESTS.status='0' GROUP BY REQUESTS.rid;

即

1
3
3

我还没说完呢!我还想要一个字符串中的相应名称:

SELECT GROUP_CONCAT(USERS.name SEPARATOR '\n') FROM USERS,USERS_G,REQUESTS
WHERE uid=u_id AND r_id=rid AND status=0 GROUP BY rid;

为了让事情更全面一点，我想要以下结果用一个查询？

 Level       Rid   Holder Name       Count       Concat
---------------------------------------------------------------
 1            96      John              1          John
 1            97      John              3          John  Peter Helen
 1            98      Peter             3          John  Peter Helen 

实现这个目标可行吗？很明显，这个查询必须尽快执行。

提前致谢

Answer 1

最简单的解决方案是在所有 3 个查询中显示选择 rid 并将查询联合在一起。例如。像这样:

SELECT r.*, counts.count, names.concat

FROM (
    SELECT lid,rid,name FROM REQUESTS JOIN USERS_G 
        ON REQUESTS.rid = USERS_G.r_id AND flag=1 AND status='0'
        JOIN USERS 
        ON USERS_G.u_id=USERS.uid
        JOIN LEVEL 
        ON LEVEL.lid=USERS.l_id AND LEVEL.lid='1' 
    ) AS r
JOIN (
    SELECT rid, COUNT(*) as count FROM REQUESTS JOIN USERS_G 
        ON REQUESTS.rid = USERS_G.r_id
        WHERE REQUESTS.status='0' GROUP BY REQUESTS.rid
    ) AS counts ON counts.rid = r.rid
JOIN (
    SELECT rid, GROUP_CONCAT(USERS.name SEPARATOR '\n') AS concat FROM USERS,USERS_G,REQUESTS
    WHERE uid=u_id AND r_id=rid AND status=0 GROUP BY rid;
    ) AS names ON names.rid = r.rid