SQL实现特定行数据提取并转换为两种指定输出格式的技术问询

嘿，这两个SQL输出需求都可以通过行转列、分组和Union操作来实现，我给你分别拆解并提供可运行的代码：

这个需求的核心是把同一filekey下的hourstype 1和2合并成一行，剩下的每个hourstype单独占一行，最后把两部分结果拼接起来。

-- 先处理hourstype 1和2的合并行
SELECT
    filekey,
    MAX(CASE WHEN hourstype = 1 THEN hours ELSE '' END) AS hours1,
    MAX(CASE WHEN hourstype = 2 THEN hours ELSE '' END) AS hours2,
    '' AS otherhourstype,
    '' AS otherhourstotal
FROM Table1
WHERE hourstype IN (1, 2)
GROUP BY filekey

UNION ALL

-- 处理hourstype 3及以上的单独行
SELECT
    filekey,
    '' AS hours1,
    '' AS hours2,
    CAST(hourstype AS CHAR) AS otherhourstype,
    CAST(hours AS CHAR) AS otherhourstotal
FROM Table1
WHERE hourstype >= 3
ORDER BY filekey, otherhourstype;

代码说明： #

• 第一部分用GROUP BY + CASE WHEN聚合，把同一个filekey下的1、2类型的hours分别放到hours1和hours2列，空值用''填充；
• 第二部分直接筛选出3及以上的记录，补全hours1和hours2的空值，把hourstype和hours转成字符串类型保证格式统一；
• 最后用UNION ALL合并两部分结果，排序让输出更整齐。

这个需求需要把同一filekey下的所有hourstype按从小到大的顺序，横向扩展成列（最多8种）。我们可以用条件聚合结合窗口函数来实现：

SELECT
    fk.filekey,
    -- 固定处理hourstype 1和2
    MAX(CASE WHEN t.hourstype = 1 THEN t.hours ELSE '' END) AS hours1,
    MAX(CASE WHEN t.hourstype = 2 THEN t.hours ELSE '' END) AS hours2,
    -- 按顺序扩展剩余的hourstype（最多8种，这里示例写至第6组，可按需增加到8组）
    MAX(CASE WHEN t.sorted_rn = 1 THEN t.hourstype ELSE '' END) AS difhrstype1,
    MAX(CASE WHEN t.sorted_rn = 1 THEN t.hours ELSE '' END) AS difhrstotal1,
    MAX(CASE WHEN t.sorted_rn = 2 THEN t.hourstype ELSE '' END) AS difhrstype2,
    MAX(CASE WHEN t.sorted_rn = 2 THEN t.hours ELSE '' END) AS difhrstotal2,
    MAX(CASE WHEN t.sorted_rn = 3 THEN t.hourstype ELSE '' END) AS difhrstype3,
    MAX(CASE WHEN t.sorted_rn = 3 THEN t.hours ELSE '' END) AS difhrstotal3,
    MAX(CASE WHEN t.sorted_rn = 4 THEN t.hourstype ELSE '' END) AS difhrstype4,
    MAX(CASE WHEN t.sorted_rn = 4 THEN t.hours ELSE '' END) AS difhrstotal4,
    MAX(CASE WHEN t.sorted_rn = 5 THEN t.hourstype ELSE '' END) AS difhrstype5,
    MAX(CASE WHEN t.sorted_rn = 5 THEN t.hours ELSE '' END) AS difhrstotal5,
    MAX(CASE WHEN t.sorted_rn = 6 THEN t.hourstype ELSE '' END) AS difhrstype6,
    MAX(CASE WHEN t.sorted_rn = 6 THEN t.hours ELSE '' END) AS difhrstotal6
FROM (
    -- 子查询：给每个filekey下的hourstype>=3的记录按从小到大排序，生成行号
    SELECT
        filekey,
        hourstype,
        hours,
        ROW_NUMBER() OVER (PARTITION BY filekey ORDER BY hourstype) AS sorted_rn
    FROM Table1
    WHERE hourstype >= 3
) AS t
RIGHT JOIN (SELECT DISTINCT filekey FROM Table1) AS fk 
    ON t.filekey = fk.filekey
GROUP BY fk.filekey;

兼容老版本SQL（无窗口函数）的写法： #

如果你的数据库不支持ROW_NUMBER()窗口函数（比如MySQL 5.x），可以用变量来生成排序行号：

SELECT
    fk.filekey,
    MAX(CASE WHEN t.hourstype = 1 THEN t.hours ELSE '' END) AS hours1,
    MAX(CASE WHEN t.hourstype = 2 THEN t.hours ELSE '' END) AS hours2,
    MAX(CASE WHEN t.rn = 1 THEN t.hourstype ELSE '' END) AS difhrstype1,
    MAX(CASE WHEN t.rn = 1 THEN t.hours ELSE '' END) AS difhrstotal1,
    MAX(CASE WHEN t.rn = 2 THEN t.hourstype ELSE '' END) AS difhrstype2,
    MAX(CASE WHEN t.rn = 2 THEN t.hours ELSE '' END) AS difhrstotal2,
    -- 继续扩展到最多8组
    MAX(CASE WHEN t.rn = 6 THEN t.hourstype ELSE '' END) AS difhrstype6,
    MAX(CASE WHEN t.rn = 6 THEN t.hours ELSE '' END) AS difhrstotal6
FROM (SELECT DISTINCT filekey FROM Table1) AS fk
LEFT JOIN (
    SELECT
        filekey,
        hourstype,
        hours,
        @rn := IF(@prev_file = filekey, @rn + 1, 1) AS rn,
        @prev_file := filekey
    FROM Table1, (SELECT @prev_file := '', @rn := 0) AS vars
    WHERE hourstype >= 3
    ORDER BY filekey, hourstype
) AS t ON fk.filekey = t.filekey
GROUP BY fk.filekey;

代码说明： #

• 子查询先给每个filekey下的hourstype>=3的记录按顺序生成行号，保证从小到大排列；
• 外部查询用MAX(CASE WHEN)把每个行号对应的hourstype和hours转成横向的列；
• 用RIGHT JOIN/LEFT JOIN确保即使某个filekey没有>=3的hourstype，也能输出一行带空值的记录；
• 如果需要支持最多8种hourstype，只要继续添加对应的MAX(CASE WHEN)列即可。

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