mysql - group，但显示列的所有行 [英] mysql - group, but show all row of a column

问题描述

我想分组字段，但想显示名为 remaining 的所有列：

这里是没有分组的当前输出：

以及输出结果：

< img src =https://i.stack.imgur.com/pKozD.pngalt =在这里输入图片描述>

我想当我分组时：

这里是mysql的查询：

  SELECT staff.name，
 staff.designation_id_designation，
 staff.status_id_status，
 Concat_Ws（'='，leave_remain.leave_type_id_leave_type，leave_remain.days）仅剩
 
 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依然为
 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

其他链接（s ）

• MySQL GROUP_CONCAT

I want to group fields but want to show all row of a column called remain:

here is current output without grouping:

And output with group:

And I want This when I group:

Here is mysql query:

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    

解决方案

use 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

other Link(s)

• MySQL GROUP_CONCAT

这篇关于mysql - group，但显示列的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！