MySQL GROUP_CONCAT中的条件

Question

我有一个MySQL表，其中存储了请求信息。

+--------+-------------+-----+
| req_id | req_name    | ... |
+--------+-------------+-----+
| 1      | testrequest | ... |
+--------+-------------+-----+

以及一个表格，该表格列出了对这些请求的投票结果。

+--------+-----------+----------+
| 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;

我的问题是，如何在同一行中获取两个不同值的group_concat？

非常感谢！

- Rapsey

---

"Order by" 与 "group_concat" 协同工作。 - safarov

Answer 1

试试这个：

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 |
+--------+-------------+---------------------+--------------+

- Mosty Mostacho

---

嘿，兄弟。如果多行中有“approved”设置为true或false，我该如何选择其中一个vote_names。我遇到了一种奇怪的情况，因为它们具有相同的值，所以多个行被连接成一个。在这种情况下，我只想选择一行。我还有另一列是唯一的，但没有在语句中使用它。当我使用它时，MySQL会抱怨它不是group_by的一部分。 - TheRealChx101

Answer 2

我尝试在另一个查询中重复使用您的查询

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

- Chetter Hummin

---

谢谢您的快速回复！您认为在不使用子查询的情况下实现这一点是否可能？因为如果我没记错的话，MySQL不允许在视图的FROM子句中使用子查询。 - Rapsey

啊，好的。我得去研究一下。 - Chetter Hummin