Oracle SQL：如何关联无通用unid的两张表并获取整批集装箱信息

嘿，针对你遇到的JOB和SEA表关联需求，我来拆解下解决方案：

首先看你的数据逻辑：JOB表中同一lotno下的多条记录（对应不同shipno），需要通过同批次里关联SEA的那条JOB记录（也就是unid=10010的那行）来间接关联到SEA表，最终要给每个shipno展示整批的集装箱类型和数量。

核心关联逻辑梳理 #

从样本数据能看出来：

• JOB表前9行的lotno都是SHSEM01198，对应不同的shipno
• 第10行（unid=10010）的lotno也是这个值，它的unid正好是SEA表job_unid的关联键
• 所以路径是：每个JOB主记录 → 通过lotno关联到同批次的SEA绑定JOB记录 → 通过unid关联到SEA表

具体SQL实现 #

我给你两种可行的写法，都能得到你想要的结果：

方法1：用CTE（公共表表达式）拆分逻辑，更易读

WITH container_stats AS (
    -- 先统计每个job_unid下的集装箱类型和数量
    SELECT 
        job_unid,
        conttype,
        COUNT(*) AS qty
    FROM SEA
    GROUP BY job_unid, conttype
),
job_link AS (
    -- 关联JOB表自身，找到每个shipno对应的SEA绑定记录
    SELECT 
        j_main.lotno,
        j_main.shipno,
        j_main.etd,
        j_main.eta,
        j_sea.unid AS sea_job_id
    FROM JOB j_main
    JOIN JOB j_sea 
        ON j_main.lotno = j_sea.lotno
        -- 筛选出确实关联SEA的JOB记录（避免误关联）
        AND EXISTS (SELECT 1 FROM SEA WHERE job_unid = j_sea.unid)
)
-- 最终整合数据，行转列展示集装箱信息
SELECT 
    jl.lotno,
    jl.shipno,
    jl.etd,
    jl.eta,
    MAX(CASE WHEN cs.conttype = '40HC' THEN cs.conttype END) AS Conttype1,
    MAX(CASE WHEN cs.conttype = '40HC' THEN cs.qty END) AS Conttype1_qty,
    MAX(CASE WHEN cs.conttype = '20ST' THEN cs.conttype END) AS Conttype2,
    MAX(CASE WHEN cs.conttype = '20ST' THEN cs.qty END) AS Conttype2_Qty,
    MAX(CASE WHEN cs.conttype NOT IN ('40HC','20ST') THEN cs.conttype END) AS Conttype3,
    MAX(CASE WHEN cs.conttype NOT IN ('40HC','20ST') THEN cs.qty END) AS Conttype3_Qty
FROM job_link jl
JOIN container_stats cs 
    ON jl.sea_job_id = cs.job_unid
GROUP BY jl.lotno, jl.shipno, jl.etd, jl.eta
ORDER BY jl.shipno;

方法2：子查询直接关联，更简洁

SELECT 
    j_main.lotno,
    j_main.shipno,
    j_main.etd,
    j_main.eta,
    -- 用CASE语句行转列，提取对应类型和数量
    MAX(CASE WHEN s.conttype = '40HC' THEN s.conttype END) AS Conttype1,
    COUNT(CASE WHEN s.conttype = '40HC' THEN 1 END) AS Conttype1_qty,
    MAX(CASE WHEN s.conttype = '20ST' THEN s.conttype END) AS Conttype2,
    COUNT(CASE WHEN s.conttype = '20ST' THEN 1 END) AS Conttype2_Qty,
    MAX(CASE WHEN s.conttype NOT IN ('40HC','20ST') THEN s.conttype END) AS Conttype3,
    COUNT(CASE WHEN s.conttype NOT IN ('40HC','20ST') THEN 1 END) AS Conttype3_Qty
FROM JOB j_main
-- 关联到同批次的SEA绑定JOB记录
JOIN JOB j_sea 
    ON j_main.lotno = j_sea.lotno
    AND EXISTS (SELECT 1 FROM SEA WHERE job_unid = j_sea.unid)
-- 关联到SEA表获取集装箱数据
JOIN SEA s ON j_sea.unid = s.job_unid
-- 按每个shipno分组，确保每个记录展示整批统计
GROUP BY j_main.lotno, j_main.shipno, j_main.etd, j_main.eta
ORDER BY j_main.shipno;

关键细节说明 #

1. 自关联JOB表：通过lotno把每个shipno的记录和同批次中绑定SEA的记录连起来，这是解决无直接通用ID的核心
2. 行转列处理：用CASE配合MAX/COUNT把SEA表中多行的集装箱类型转换成你需要的多列格式，后续如果有新的集装箱类型，直接加对应的CASE分支即可
3. EXISTS筛选：确保只关联确实和SEA表有绑定的JOB记录，避免无效关联

执行这两个SQL都能得到你期望的结果，每个shipno都会显示整批的40HC（3个）、20ST（2个）的信息，没有对应数据的Conttype3会为空。

内容的提问来源于stack exchange，提问作者p.yee