MySQL技术需求：按name分组生成kodemat列头展示交易数据

数据表结构 #

1. tblcustomer表 #

2. tbltransaction表 #

预期查询结果 #

按name分组，生成kodemat 1、kodemat 2、kodemat 3列头并统计sumqty：

当前查询语句及问题 #

当前使用的SQL语句：

SELECT a.id,a.name,group_concat(a.kodemat order by a.id limit 5 ) kodemat, sum(a.qty) qty, datecreated,custname 
from tbltransaction a join tblcustomer b on b.id = a.custID 
where a.qty >'0' group by a.name;

查询结果（不符合预期，kodemat为拼接字符串而非分列展示）：

解决方案SQL语句 #

SELECT 
    b.datecreated,
    t.name,
    MAX(CASE WHEN t.rn = 1 THEN t.kodemat ELSE 0 END) AS `kodemat 1`,
    MAX(CASE WHEN t.rn = 2 THEN t.kodemat ELSE 0 END) AS `kodemat 2`,
    MAX(CASE WHEN t.rn = 3 THEN t.kodemat ELSE 0 END) AS `kodemat 3`,
    SUM(t.qty) AS sumqty
FROM (
    SELECT 
        custID,
        name,
        qty,
        kodemat,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
    FROM tbltransaction
    WHERE qty > 0
) t
JOIN tblcustomer b ON b.id = t.custID
GROUP BY t.name, b.datecreated
ORDER BY t.name DESC;

逻辑说明 #

1. 子查询中通过ROW_NUMBER()窗口函数，按name分组、id排序，给每个分组内的kodemat生成顺序序号。
2. 外层查询用CASE结合MAX聚合函数，提取序号1、2、3对应的kodemat，无对应值时填充0。
3. 同时聚合计算sumqty，关联tblcustomer获取datecreated，最终按name分组得到符合预期的分列结果。

内容的提问来源于stack exchange，提问作者den.bagoes