如何用GROUP_CONCAT实现按年份和职位聚合well字段的SQL查询

表结构 #

CREATE TABLE cities  
(
    `year` INTEGER,
    `job` TEXT,
    `well` TEXT
);

INSERT INTO cities (`year`, `job`, `well`)
VALUES ('2001', 'A', 'G1'),
       ('2001', 'S', 'G1'),
       ('2001', 'S', 'G2'),
       ('2002', 'S', 'G1'),
       ('2008', 'S', 'G2');

原查询及结果 #

原SQL语句：

SELECT year, GROUP_CONCAT( job || '-' || well, ',  ') AS id_city
FROM cities
GROUP BY year;

执行结果：

year    id_city
--------------------------
2001    A-G1,  S-G1,  S-G2
2002    S-G1
2008    S-G2

期望结果 #

year   job   well
---------------------
2001    A     G1
2001    S     G1,G2
2002    S     G1
2008    S     G2

调整后的SQL语句 #

SELECT 
    year,
    job,
    GROUP_CONCAT(well, ',') AS well
FROM cities
GROUP BY year, job;

改动说明 #

1. 分组条件调整：将GROUP BY year改为GROUP BY year, job，按年份和职位的组合进行分组，确保同一year+job下的well会被聚合。
2. 聚合函数修改：把GROUP_CONCAT( job || '-' || well, ', ')替换为GROUP_CONCAT(well, ',')，仅对同一分组内的well字段进行逗号拼接，job作为分组字段直接出现在结果中，无需重复拼接。

执行上述调整后的SQL，即可得到期望的结果。

内容的提问来源于stack exchange，提问作者PMR