MySQL - 分组但显示一列的所有行

Question

我想对字段进行分组，但是想显示名为remain的列的所有行：

这是未分组的当前输出：

分组后的输出结果如下：

当我进行分组时，我希望得到以下结果：

这是mysql查询语句：

SELECT staff.name,
  staff.designation_id_designation,
  staff.status_id_status,
  Concat_Ws('=', leave_remain.leave_type_id_leave_type, leave_remain.days) AS
  remain
FROM staff INNER JOIN
  leave_remain ON staff.id_staff = leave_remain.staff_id_staff
GROUP BY staff.name    

- user007

Answer 1

使用 GROUP_CONCAT

SELECT  staff.name,
        staff.designation_id_designation,
        staff.status_id_status,
        GROUP_CONCAT(Concat_Ws('=', leave_remain.leave_type_id_leave_type, leave_remain.days)) AS remain
FROM    staff 
        INNER JOIN leave_remain 
            ON staff.id_staff = leave_remain.staff_id_staff
GROUP   BY staff.name, staff.designation_id_designation, staff.status_id_status  

• SQLFiddle演示

其他链接

• MySQL GROUP_CONCAT

- John Woo

---

将所有数字转换为字符串，Concat_Ws('=', CAST(leave_remain.leave_type_id_leave_type AS VARCHAR(30)), CAST(leave_remain.days AS VARCHAR(30)))。 - John Woo

你正在使用哪个版本的MySQL？ - John Woo

哦，这是语法错误，应该使用CHAR而不是VARCHAR。http://www.sqlfiddle.com/#!2/729f7/7 - John Woo

Answer 2

试试这个：

SELECT staff.name,
  staff.designation_id_designation,
  staff.status_id_status,
  GROUP_CONCAT(Concat_Ws('=', leave_remain.leave_type_id_leave_type, leave_remain.days) SEPARATOR ' ') AS remain
FROM staff INNER JOIN
  leave_remain ON staff.id_staff = leave_remain.staff_id_staff
GROUP BY staff.designation_id_designation,
         staff.name,
         staff.status_id_status;

- Hamlet Hakobyan

Answer 3

试一试这个，我们需要使用 group_concat 分隔符为 ' '。

SELECT staff.name,
  staff.designation_id_designation,
  staff.status_id_status,
  GROUP_CONCAT(Concat_Ws('=', leave_remain.leave_type_id_leave_type, leave_remain.days) SEPARATOR ' ') AS remain
FROM staff 
INNER JOIN
  leave_remain 
ON 
staff.id_staff = leave_remain.staff_id_staff
GROUP BY staff.designation_id_designation,
         staff.name,
         staff.status_id_status;

- user2001117