合并具有相同ID的多个mysql行 [英] Merge multiple mysql rows having same id

问题描述

我有一个这样的表:

id  employee_id contract_id      month    year    d1     d2      d3
1          25        1                11     2011    1      01      01
2          16        5                11     2011    1      11      0
3          29        3                11     2011    1      001     100
1          25        4                11     2011    0      11      011

假设我需要month = '11'和year ='2011'的数据，那么对于所有具有相同'employee_id'的行，数据应该像这样合并:

Suppose I need data for month='11' AND year='2011', then for all rows having the same 'employee_id', the data should merge like this:

id  employee_id contract_id      month    year    d1     d2      d3
1          25        1,4            11       2011    1,0    01,11   01,011
2          16        5              11       2011    1      11      0
3          29        3              11       2011    1      001     100

我正在尝试GROUP_CONCAT，但找不到查询.请帮忙.

I was trying GROUP_CONCAT but couldn't figure out the query. Please help.

推荐答案

SELECT
    id,
    employee_id,
    GROUP_CONCAT(contract_id SEPARATOR ',') AS contract_ids,
    `month`,
    `year`,
    GROUP_CONCAT(d1 SEPARATOR ',') AS d1s,
    GROUP_CONCAT(d2 SEPARATOR ',') AS d2s,
    GROUP_CONCAT(d3 SEPARATOR ',') AS d3s
FROM
    `table`
WHERE
    `month` = 11 AND `year` = 2011
GROUP BY
    employee_id

这篇关于合并具有相同ID的多个mysql行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！