多表关联下如何实现产品子部件的单行多列查询结果展示

看起来你现在的核心问题是关联BOM表后，同一产品会因为多个子部件生成多行记录，没法把三个子部件信息合并到同一行的三列里对吧？我来帮你调整SQL，实现行转列的效果。

原查询的问题分析 #

1. 多行重复问题：直接LEFT JOIN BOMU01T会让每个子部件对应一条父产品记录，导致同一订单产品出现多行，没法合并成单行展示
2. 子查询风险：你写的子查询如果遇到多个匹配的STOCK00记录，会直接抛出“单行子查询返回多行”的错误，而且没做聚合处理
3. 语法小问题：外层子查询没有别名，虽然Oracle允许，但可读性差；RAWM-01这类列名包含减号，需要用双引号包裹，否则会被解析成算术表达式报错

修正后的SQL（通用行转列写法，兼容大多数数据库） #

SELECT 
    SL.DOCNO AS DOCUMENT_NO,
    SL.CUSTOMERCODE AS CUSTOMER_CODE,
    C0.NAME AS CUSTOMER_NAME,
    SL.CODE AS PRODUCT_CODE,
    ST.NAME AS PRODUCT_NAME,
    SL.QUANTITY AS ORDER_QUANTITY,
    SL.QT_SHIPPED AS SHIPPED_QUANTITY,
    (SL.QUANTITY - SL.QT_SHIPPED) AS OPEN_ORDER_QUANTITY,
    NVL(TO_CHAR(TO_DATE(SL.DELVRDATE,'YYYY/MM/DD'),'DD/MM/YYYY'), '0') AS DELIVERY_DATE,
    -- 用MAX+CASE筛选PAINTED类型的子部件
    MAX(CASE WHEN TRIM(S0.GK_7) = 'PAINTED' THEN S0.CODE END) AS "PAINTED",
    -- 注意列名含减号，需要双引号包裹
    MAX(CASE WHEN TRIM(S0.GK_13) = 'RAWM-1' THEN S0.CODE END) AS "RAWM-01",
    MAX(CASE WHEN TRIM(S0.GK_16) = 'RAWM-02' THEN S0.CODE END) AS "RAWM-02"
FROM STOCK40T SL
LEFT JOIN STOCK00 ST ON TRIM(ST.CODE) = TRIM(SL.CODE)
LEFT JOIN CUSTOM00 C0 ON TRIM(C0.CODE) = TRIM(SL.CUSTOMERCODE)
LEFT JOIN BOMU01T BOM ON BOM.BOMREC_CODE = SL.CODE
LEFT JOIN STOCK00 S0 ON S0.CODE = BOM.BOMREC_SOURCECODE
-- 按主表的唯一键分组，确保单行展示
GROUP BY 
    SL.DOCNO,
    SL.CUSTOMERCODE,
    C0.NAME,
    SL.CODE,
    ST.NAME,
    SL.QUANTITY,
    SL.QT_SHIPPED,
    NVL(TO_CHAR(TO_DATE(SL.DELVRDATE,'YYYY/MM/DD'),'DD/MM/YYYY'), '0')
ORDER BY PRODUCT_CODE, DELIVERY_DATE ASC;

针对Oracle的简化写法（用PIVOT） #

如果你用的是Oracle数据库，可以用内置的PIVOT函数，写法更简洁直观：

SELECT * FROM (
    SELECT 
        SL.DOCNO AS DOCUMENT_NO,
        SL.CUSTOMERCODE AS CUSTOMER_CODE,
        C0.NAME AS CUSTOMER_NAME,
        SL.CODE AS PRODUCT_CODE,
        ST.NAME AS PRODUCT_NAME,
        SL.QUANTITY AS ORDER_QUANTITY,
        SL.QT_SHIPPED AS SHIPPED_QUANTITY,
        (SL.QUANTITY - SL.QT_SHIPPED) AS OPEN_ORDER_QUANTITY,
        NVL(TO_CHAR(TO_DATE(SL.DELVRDATE,'YYYY/MM/DD'),'DD/MM/YYYY'), '0') AS DELIVERY_DATE,
        S0.CODE AS PART_CODE,
        -- 先把GK字段转成我们需要的分类标签
        CASE 
            WHEN TRIM(S0.GK_7) = 'PAINTED' THEN 'PAINTED'
            WHEN TRIM(S0.GK_13) = 'RAWM-1' THEN 'RAWM-01'
            WHEN TRIM(S0.GK_16) = 'RAWM-02' THEN 'RAWM-02'
        END AS PART_TYPE
    FROM STOCK40T SL
    LEFT JOIN STOCK00 ST ON TRIM(ST.CODE) = TRIM(SL.CODE)
    LEFT JOIN CUSTOM00 C0 ON TRIM(C0.CODE) = TRIM(SL.CUSTOMERCODE)
    LEFT JOIN BOMU01T BOM ON BOM.BOMREC_CODE = SL.CODE
    LEFT JOIN STOCK00 S0 ON S0.CODE = BOM.BOMREC_SOURCECODE
)
-- 按PART_TYPE转成列
PIVOT (
    MAX(PART_CODE) FOR PART_TYPE IN ('PAINTED' AS "PAINTED", 'RAWM-01' AS "RAWM-01", 'RAWM-02' AS "RAWM-02")
)
ORDER BY PRODUCT_CODE, DELIVERY_DATE ASC;

关键说明 #

• GROUP BY/PIVOT的作用：通过分组或者行转列函数，把同一父产品的多个子部件记录合并成一行，每个子部件对应单独的列
• 列名处理：因为RAWM-01这类列名包含特殊字符减号，必须用双引号包裹，否则Oracle会把它解析成RAWM - 1的算术表达式，导致报错
• NULL值处理：如果某个子部件不存在，对应的列会显示NULL，你可以用NVL(MAX(...), '无')来替换成自定义的默认值

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