One post tagged with "arch"

WITH RECURSIVE
    awards_sort AS (
        SELECT '金奖' AS award, 1 AS sort_order UNION ALL
        SELECT '银奖', 2 UNION ALL
        SELECT '铜奖', 3 UNION ALL
        SELECT '未来之星奖', 4 UNION ALL
        SELECT '希望之星', 5 UNION ALL
        SELECT '未获奖', 6
    ),
    detail_rows AS (
        SELECT
            id AS detail_id,
            group_name,
            CASE
                WHEN COALESCE(absent_final, 0) = 1 THEN '希望之星'
                WHEN award IN ('金奖','银奖','铜奖','未来之星奖') THEN award
                ELSE '未获奖'
                END AS award_label,
            name,
            COALESCE(teacher, '') AS teacher,
            num,
            CASE WHEN group_name = '弦乐团' THEN name ELSE '' END AS ensemble_name,
            member_list,
            COALESCE(contact, '') AS contact,
            COALESCE(address, '') AS address
        FROM v_2025_final
    ),
    group_totals AS (
        SELECT group_name, award_label, COUNT(*) AS people_count, SUM(num) AS total_num
        FROM detail_rows
        GROUP BY group_name, award_label
    ),
    member_split AS (
        SELECT
            dr.detail_id,
            dr.group_name,
            dr.award_label,
            1 AS member_order,
            CASE WHEN instr(dr.member_list,'、') > 0
                     THEN substr(dr.member_list,1,instr(dr.member_list,'、')-1)
                 ELSE dr.member_list END AS member_detail,
            CASE WHEN instr(dr.member_list,'、') > 0
                     THEN substr(dr.member_list,instr(dr.member_list,'、')+1)
                 ELSE NULL END AS remainder
        FROM detail_rows dr
        WHERE dr.member_list IS NOT NULL AND dr.member_list <> ''
        UNION ALL
        SELECT
            detail_id,
            group_name,
            award_label,
            member_order + 1,
            CASE WHEN instr(remainder,'、') > 0
                     THEN substr(remainder,1,instr(remainder,'、')-1)
                 ELSE remainder END,
            CASE WHEN instr(remainder,'、') > 0
                     THEN substr(remainder,instr(remainder,'、')+1)
                 ELSE NULL END
        FROM member_split
        WHERE remainder IS NOT NULL AND remainder <> ''
    ),
    member_lines AS (
        SELECT detail_id, group_name, award_label, member_order, member_detail
        FROM member_split
    )
SELECT
    group_name,
    award_label AS award,
    row_type,
    people_count,
    total_num,
    name,
    teacher,
    num,
    ensemble_name,
    member_detail,
    contact,
    address
FROM (
         -- 小计行
         SELECT
             g.group_name,
             g.award_label,
             '小计' AS row_type,
             g.people_count,
             g.total_num,
             '' AS name,
             '' AS teacher,
             NULL AS num,
             '' AS ensemble_name,
             NULL AS member_detail,
             '' AS contact,
             '' AS address,
             s.sort_order,
             -1 AS detail_seq,
             0 AS row_order,
             0 AS member_order
         FROM group_totals g
                  JOIN awards_sort s ON s.award = g.award_label

         UNION ALL

         -- 参赛明细
         SELECT
             d.group_name,
             d.award_label,
             '明细' AS row_type,
             NULL,
             NULL,
             d.name,
             d.teacher,
             d.num,
             d.ensemble_name,
             NULL,
             d.contact,
             d.address,
             s.sort_order,
             d.detail_id,
             1 AS row_order,
             0 AS member_order
         FROM detail_rows d
                  JOIN awards_sort s ON s.award = d.award_label

         UNION ALL

         -- 名单
         SELECT
             m.group_name,
             m.award_label,
             '名单' AS row_type,
             NULL,
             NULL,
             '' AS name,
             '' AS teacher,
             NULL AS num,
             '' AS ensemble_name,
             m.member_detail,
             '' AS contact,
             '' AS address,
             s.sort_order,
             m.detail_id,
             2 AS row_order,
             m.member_order
         FROM member_lines m
                  JOIN awards_sort s ON s.award = m.award_label
     )
ORDER BY
    group_name,
    sort_order,
    detail_seq,
    row_order,
    member_order;

WITH awards_sort(award, sort_order) AS (
    VALUES ('金奖',1),('银奖',2),('铜奖',3),('未来之星奖',4),('希望之星',5),('未获奖',6)
),
     detail_rows AS (
         SELECT
             id AS detail_id,
             group_name,
             CASE
                 WHEN COALESCE(absent_final,0)=1 THEN '希望之星'
                 WHEN award IN ('金奖','银奖','铜奖','未来之星奖') THEN award
                 ELSE '未获奖'
                 END AS award_label,
             name,
             COALESCE(teacher,'') AS teacher,
             num,
             CASE WHEN group_name='弦乐团' THEN name ELSE '' END AS ensemble_name,
             member_list,
             COALESCE(contact,'') AS contact,
             COALESCE(address,'') AS address
         FROM v_2025_final
     ),
     group_totals AS (
         SELECT group_name, award_label, COUNT(*) AS people_count, SUM(num) AS total_num
         FROM detail_rows
         GROUP BY 1,2
     ),
     member_lines AS (
         SELECT
             d.detail_id,
             d.group_name,
             d.award_label,
             gs.generate_series AS member_order,
             TRIM(list_element(s.ss, gs.generate_series)) AS member_detail
         FROM detail_rows d
                  CROSS JOIN LATERAL (SELECT string_split(member_list, '、') AS ss) s
                  CROSS JOIN LATERAL generate_series(1, array_length(s.ss)) gs
WHERE member_list IS NOT NULL AND member_list <> ''
    )
SELECT
    group_name,
    award_label AS award,
    row_type,
    people_count,
    total_num,
    name,
    teacher,
    num,
    ensemble_name,
    member_detail,
    contact,
    address
FROM (
         SELECT g.group_name, g.award_label, '小计' AS row_type,
                g.people_count, g.total_num,
                '' AS name, '' AS teacher, NULL AS num, '' AS ensemble_name,
                NULL AS member_detail, '' AS contact, '' AS address,
                s.sort_order, -1 AS detail_seq, 0 AS row_order, 0 AS member_order
         FROM group_totals g JOIN awards_sort s ON s.award = g.award_label

         UNION ALL
         SELECT d.group_name, d.award_label, '明细',
                NULL, NULL,
                d.name, d.teacher, d.num, d.ensemble_name,
                NULL, d.contact, d.address,
                s.sort_order, d.detail_id, 1, 0
         FROM detail_rows d JOIN awards_sort s ON s.award = d.award_label

         UNION ALL
         SELECT m.group_name, m.award_label, '名单',
                NULL, NULL,
                '' AS name, '' AS teacher, NULL, '' AS ensemble_name,
                m.member_detail, '' AS contact, '' AS address,
                s.sort_order, m.detail_id, 2, m.member_order
         FROM member_lines m JOIN awards_sort s ON s.award = m.award_label
     )
ORDER BY group_name, sort_order, detail_seq, row_order, member_order;