SQL按STATE分组用CONCAT拼接得票结果不符合预期求助

原代码问题 #

你当前的查询GROUP BY v.state, c.fname, c.lname的分组粒度是「单个州+单个候选人」，最终返回的是每个候选人在对应州的单独得票记录，没有对同州的多条候选人记录做二次字符串聚合，因此无法实现按州维度合并展示的效果。

修正思路 #

查询分两层实现：

1. 内层查询：先按「州+候选人」分组，统计每个候选人在对应州的得票数，拼接生成单条姓名 x 得票数格式的字符串
2. 外层查询：再按州分组，使用数据库对应的字符串聚合函数，将同州下的所有单条得票记录用逗号拼接成最终的VOTES字段

注意：不同数据库的字符串聚合函数不同：MySQL用GROUP_CONCAT，PostgreSQL、SQL Server用STRING_AGG，Oracle用LISTAGG

可直接运行的修正代码（MySQL环境） #

SELECT
    stat.state,
    GROUP_CONCAT(stat.candidate_vote SEPARATOR ', ') AS VOTES
FROM (
    SELECT
        v.state,
        CONCAT(c.fname, ' ', c.lname, ' x ', COUNT(*)) AS candidate_vote
    FROM candidates c
    INNER JOIN votes v ON c.id = v.id
    GROUP BY v.state, c.id, c.fname, c.lname
) AS stat
GROUP BY stat.state;

执行后返回的结果和期望示例完全一致：

如果使用其他数据库，只需要把外层的GROUP_CONCAT(stat.candidate_vote SEPARATOR ', ')替换为对应数据库的字符串聚合语法即可，例如PostgreSQL/SQL Server环境替换为STRING_AGG(stat.candidate_vote, ', ')。

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