MariaDB：按成员生成前5条成绩的透视表方案问询

假设你的表名为scores，可以通过窗口函数+条件聚合的方式实现需求，具体方案如下：

实现逻辑 #

1. 给每个成员的成绩按日期排序并标记行号
   用ROW_NUMBER()窗口函数按成员分组，将每个成员的成绩按日期升序排列，生成1、2、3...的行号，确保只取前5条数据。
2. 行转列生成透视表
   通过条件聚合，将每个行号对应的成绩转为单独的列，不足5条的位置用COALESCE替换为0（或保留NULL）。

完整SQL代码 #

SELECT
    member AS 成员,
    COALESCE(MAX(CASE WHEN rn = 1 THEN score END), 0) AS 成绩1,
    COALESCE(MAX(CASE WHEN rn = 2 THEN score END), 0) AS 成绩2,
    COALESCE(MAX(CASE WHEN rn = 3 THEN score END), 0) AS 成绩3,
    COALESCE(MAX(CASE WHEN rn = 4 THEN score END), 0) AS 成绩4,
    COALESCE(MAX(CASE WHEN rn = 5 THEN score END), 0) AS 成绩5
FROM (
    SELECT
        date,
        member,
        score,
        ROW_NUMBER() OVER (
            PARTITION BY member
            ORDER BY STR_TO_DATE(date, '%d-%m-%Y') ASC
        ) AS rn
    FROM scores
) AS ranked_scores
WHERE rn <= 5
GROUP BY member
ORDER BY member;

代码说明 #

• STR_TO_DATE(date, '%d-%m-%Y')：将字符串格式的日期转为日期类型，确保排序逻辑正确（如果你的date字段已经是日期类型，可直接用date排序）。
• ROW_NUMBER() OVER (PARTITION BY member ORDER BY ...)：按成员分组，给每个成员的成绩按日期顺序标记行号，保证前5条是最早的5个日期的成绩。
• CASE WHEN rn = n THEN score END：筛选对应行号的成绩，配合MAX()聚合函数完成行转列。
• COALESCE(..., 0)：把无对应成绩的NULL值替换为0，若想保留NULL，去掉COALESCE即可。

结果验证 #

运行代码后会得到你期望的结果：

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