mysql - mysql-组，但显示列的所有行

我想对字段进行分组，但想显示称为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

最佳答案

使用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 Demo

其他链接

MySQL GROUP_CONCAT

关于mysql - mysql-组，但显示列的所有行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14806631/