SQL Join聚合后通过Pivot行转列生成库存资产报表问题求助

问题原因 #

• 两个独立SELECT语句无关联，第二个PIVOT查询直接读取原始Resources表，该表不存在第一个查询生成的Asset Count、Asset State字段，无法完成聚合计算，因此无返回结果
• PIVOT逻辑与需求不符：需求以产品类型为行维度，所以PIVOT的分组依据应为Product Type，Asset State是待转列的维度，不能出现在最终SELECT的非聚合字段中
• 缺少子查询/CTE封装，PIVOT无法直接调用前序查询的计算结果

修复后的PIVOT写法（适用支持CTE与标准PIVOT语法的数据库，如SQL Server、Oracle 11g+） #

WITH asset_base AS (
    SELECT  
        productType.COMPONENTTYPENAME AS "Product Type", 
        "state"."displaystate" AS "Asset State", 
        count("resource"."resourcename" ) AS "Asset Count" 
    FROM Resources resource
    LEFT JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID
    LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
    LEFT JOIN "resourcestate" "state" ON "resource"."resourcestateid" = "state"."resourcestateid"
    LEFT JOIN "resourcetype" "rtype" ON "productType"."resourcetypeid" = "rtype"."resourcetypeid"
    GROUP BY state.displaystate, productType.COMPONENTTYPENAME
)
SELECT 
    "Product Type", 
    COALESCE("In Use", 0) AS "In Use",
    COALESCE("Used - In Store", 0) AS "Used - In Store",
    COALESCE("In Store", 0) AS "In Store",
    COALESCE("New - In Store", 0) AS "New - In Store",
    COALESCE("Damaged", 0) AS "Damaged",
    COALESCE("Faulty", 0) AS "Faulty"
FROM asset_base
PIVOT(
    SUM("Asset Count") 
    FOR "Asset State" IN ("In Use", "Used - In Store", "In Store", "New - In Store", "Damaged", "Faulty")
) AS pivot_result;

说明：添加COALESCE是为了将无数据的状态列默认值设为0，避免返回NULL

全数据库兼容写法（不依赖PIVOT语法，适配所有老旧库存系统数据库） #

如果你的库存系统数据库不支持PIVOT语法，可以用CASE WHEN手动实现行转列，兼容性最强：

SELECT  
    productType.COMPONENTTYPENAME AS "Product Type", 
    SUM(CASE WHEN "state"."displaystate" = 'In Use' THEN 1 ELSE 0 END) AS "In Use",
    SUM(CASE WHEN "state"."displaystate" = 'Used - In Store' THEN 1 ELSE 0 END) AS "Used - In Store",
    SUM(CASE WHEN "state"."displaystate" = 'In Store' THEN 1 ELSE 0 END) AS "In Store",
    SUM(CASE WHEN "state"."displaystate" = 'New - In Store' THEN 1 ELSE 0 END) AS "New - In Store",
    SUM(CASE WHEN "state"."displaystate" = 'Damaged' THEN 1 ELSE 0 END) AS "Damaged",
    SUM(CASE WHEN "state"."displaystate" = 'Faulty' THEN 1 ELSE 0 END) AS "Faulty"
FROM Resources resource
LEFT JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID
LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
LEFT JOIN "resourcestate" "state" ON "resource"."resourcestateid" = "state"."resourcestateid"
LEFT JOIN "resourcetype" "rtype" ON "productType"."resourcetypeid" = "rtype"."resourcetypeid"
GROUP BY productType.COMPONENTTYPENAME;

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