条件在MySQL GROUP_CONCAT
我有一个请求条件在MySQL GROUP_CONCAT
+--------+-------------+-----+
| req_id | req_name | ... |
+--------+-------------+-----+
| 1 | testrequest | ... |
+--------+-------------+-----+
一个MySQL表,并与这些请求票表。
+--------+-----------+----------+
| req_id | vote_name | approved |
+--------+-----------+----------+
| 1 | User1 | 1 |
| 1 | User2 | 1 |
| 1 | User3 | 1 |
| 1 | User4 | 0 |
| 1 | User5 | 0 |
+--------+-----------+----------+
那种观点我想:
+--------+-------------+---------------------+--------------+
| req_id | req_name | approved_by | rejected_by |
+--------+-------------+---------------------+--------------+
| 1 | testrequest | User1, User2, User3 | User4, User5 |
+--------+-------------+---------------------+--------------+
然而到目前为止,我只能够做到这一点:
+--------+-------------+----------+---------------------+
| req_id | req_name | approved | by |
+--------+-------------+----------+---------------------+
| 1 | testrequest | YES | User1, User2, User3 |
| 1 | testrequest | NO | User4, User5 |
+--------+-------------+----------+---------------------+
我使用的查询:
SELECT requests.req_id, req_name, CASE
WHEN approved THEN 'YES'
ELSE 'NO'
END AS approved, GROUP_CONCAT(vote_name ORDER BY vote_name ASC SEPARATOR ', ') AS by
FROM requests
LEFT JOIN votes ON requests.req_id = votes.req_id
GROUP BY requests.req_id, approved
ORDER BY requests.req_id DESC;
所以我的问题是,我怎么得到2 group_c oncats在同一行中使用不同的值?
非常感谢!
试试这个:
select r.req_id, r.req_name,
group_concat(if(approved, vote_name, null) separator ', ') approvedBy,
group_concat(if(approved, null, vote_name) separator ', ') rejectedBy
from requests r
left join votes v on r.req_id = v.req_id
结果:
+--------+-------------+---------------------+--------------+
| REQ_ID | REQ_NAME | APPROVEDBY | REJECTEDBY |
+--------+-------------+---------------------+--------------+
| 1 | testrequest | User1, User2, User3 | User4, User5 |
+--------+-------------+---------------------+--------------+
真棒,谢谢!这正是我想要的。 :) – Rapsey 2012-03-23 18:11:02
这是超级有用,谢谢! – 2013-05-11 20:41:32
我试图重新使用另一个查询中查询
SELECT req_id,
req_name,
GROUP_CONCAT(case approved when 'YES' then voted_by else null end SEPARATOR ', ') AS approved_by,
GROUP_CONCAT(case approved when 'NO' then voted_by else null end SEPARATOR ', ') AS rejected_by
FROM
(
SELECT requests.req_id, req_name, CASE
WHEN approved THEN 'YES'
ELSE 'NO'
END AS approved,
GROUP_CONCAT(vote_name ORDER BY vote_name ASC SEPARATOR ', ') AS voted_by
FROM requests
LEFT JOIN votes ON requests.req_id = votes.req_id
GROUP BY requests.req_id, approved
ORDER BY requests.req_id DESC
) t
group by req_id
感谢您的快速回复!你认为有可能在不使用子查询的情况下做到这一点?因为如果我没有弄错,MySQL不允许在查看的FROM子句中使用子查询。 – Rapsey 2012-03-23 18:04:18
好的。将不得不查看它。 – 2012-03-23 18:13:56
'订购by'正在与'group_concat' – safarov 2012-03-23 17:38:16