我有以下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
级别到用户1:N，用户到用户1:N，请求到用户1:N。
我想从具有特定状态的请求中选择那些记录，同时这些请求记录的用户持有者具有特定级别。如果用户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
实现这一目标可行吗？显然，必须尽快执行此查询。
提前谢谢你
最佳答案
最简单的解决方案是在所有3个查询中显示selectrid，并将查询联合在一起。像这样的东西：
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
关于mysql - 将这三个SELECT语句组合在一起是否可行？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30196345/