如何将reservation表与disability表合并为newTable

现有表结构及数据 #

reservation表（预订残障需求统计） #

INSERT INTO reservation(rid, WHEEL_CHAIR, blind) VALUES (1,8,9);
INSERT INTO reservation(rid, WHEEL_CHAIR, blind) VALUES (2,11,12);

查询结果：

disability表（残障类型映射） #

INSERT INTO disability(hid, code, DESCRIPTION) VALUES (5,'aa', 'wheel chair');
INSERT INTO disability(hid, code, DESCRIPTION) VALUES (7,'bl', 'blind');

目标结果 #

生成newTable，数据格式如下：

INSERT INTO newTable(newID,hid,count) VALUES (1,5,8);
INSERT INTO newTable(newID,hid,count) VALUES (1,7,9);
INSERT INTO newTable(newID,hid,count) VALUES (2,5,11);
INSERT INTO newTable(newID,hid,count) VALUES (2,7,12);

实现SQL #

通用SQL方案（支持所有标准SQL数据库） #

通过CROSS JOIN将每条预订记录与所有残障类型匹配，再用CASE语句关联对应人数：

INSERT INTO newTable(newID, hid, count)
SELECT 
    r.rid AS newID,
    d.hid,
    CASE 
        WHEN d.DESCRIPTION = 'wheel chair' THEN r.WHEEL_CHAIR
        WHEN d.DESCRIPTION = 'blind' THEN r.blind
    END AS count
FROM reservation r
CROSS JOIN disability d
ORDER BY r.rid, d.hid;

简化方案（支持UNPIVOT语法的数据库：SQL Server、Oracle等） #

先通过UNPIVOT将reservation的列转成行，再关联disability表获取对应hid：

INSERT INTO newTable(newID, hid, count)
SELECT 
    up.rid AS newID,
    d.hid,
    up.count
FROM (
    SELECT rid, disability_type, count
    FROM reservation
    UNPIVOT (
        count FOR disability_type IN (WHEEL_CHAIR, blind)
    ) AS up
) up
JOIN disability d 
    ON (up.disability_type = 'WHEEL_CHAIR' AND d.DESCRIPTION = 'wheel chair')
    OR (up.disability_type = 'blind' AND d.DESCRIPTION = 'blind')
ORDER BY up.rid, d.hid;

逻辑说明 #

• 通用方案：利用CROSS JOIN生成所有预订-残障类型的组合，再通过CASE匹配对应统计值，兼容性强。
• 简化方案：先将宽表转成窄表（行转列），再通过关联匹配残障ID，写法更简洁，适合支持UNPIVOT的数据库。

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