如何在SQL中关联Name_ID与ID_列获取对应收入数据

思路说明 #

由于earnings表采用多列存储ID与对应收入的结构，需要先将其转换为行式结构，再与names表进行关联匹配。可以通过UNION ALL将每组ID-收入对拆分为单独的行，之后通过JOIN关联names表的Name_ID字段。

实现SQL代码 #

SELECT 
    n.Name_ID,
    n.Name_desc,
    e.earnings
FROM names n
JOIN (
    -- 拆分earnings表的四组ID-收入对
    SELECT ID_1 AS user_id, Earnings_1 AS earnings FROM earnings
    UNION ALL
    SELECT ID_2 AS user_id, Earnings_2 AS earnings FROM earnings
    UNION ALL
    SELECT ID_3 AS user_id, Earnings_3 AS earnings FROM earnings
    UNION ALL
    SELECT ID_4 AS user_id, Earnings_4 AS earnings FROM earnings
) e ON n.Name_ID = e.user_id;

结果说明 #

执行上述SQL后，将得到如下结果表：

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