mysql - 查询返回错误结果

Question

SELECT 
    A.id AS id, 
    Group_Concat(B.value) AS values, 
    Group_Concat(C.ac_id) AS ac_id, 
    Concat(D.first_name,' ',D.last_name) AS updatedBy
FROM A 
LEFT JOIN B 
    ON A.id = B.id 
LEFT JOIN C 
    ON A.id = C.id 
LEFT JOIN D 
    ON A.modified_by = D.user_id 
WHERE 
    A.status!='deleted' 
    AND A.created_by = '18'  
GROUP BY A.id;

表 B 和 C 有多行对应于 A.id 这就是为什么我在这些字段上使用 group_concat 但我得到那些分组列的错误结果。查询返回结果如

+---------+-------------------------+-------------------------+-------------+
| id      | values                  | ac_id                   | updatedBy   |
+---------+-------------------------+-------------------------+-------------+
|       8 | A,A,A,B,B,B,C,C,C,D,D,D | 1,5,6,1,5,6,1,5,6,1,5,6 | Abdul       |
+---------+-------------------------+-------------------------+-------------+

但我需要这样的输出

+---------+-------------------------+-------------------------+-------------+
| id      | values                  | ac_id                   | updatedBy   |
+---------+-------------------------+-------------------------+-------------+
|       8 | A,B,C,D                 | 1,5,6                   | Abdul       |
+---------+-------------------------+-------------------------+-------------+

Answer 1

在 GROUP_CONCAT() 中使用 DISTINCT。这样就不会显示重复项。

SELECT 
A.id AS id, 
Group_Concat(DISTINCT B.value) AS values, 
Group_Concat(DISTINCT  C.ac_id) AS ac_id, 
Concat(D.first_name,' ',D.last_name) AS updatedBy
FROM A 
LEFT JOIN B ON A.id = B.id 
LEFT JOIN C ON A.id = C.id 
LEFT JOIN D ON A.modified_by = D.user_id 
WHERE A.status!='deleted' AND A.created_by = '18'  GROUP BY A.id;