通过SQL查询提取单订单多产品数据生成Excel报表问题求解

解决方案 #

因为你明确每个订单最多关联2个产品，两种常用方案可以实现你要的报表字段格式：

方案1：窗口函数+条件聚合（推荐，兼容SQL Server/MySQL 8.0+/PostgreSQL等主流新版本数据库） #

先给关联的产品按订单维度生成1、2的序号，再通过聚合行转列得到单独的产品字段：

SELECT 
    o.OrderNo AS 'Order no',
    o.OrderValue AS 'Order value',
    MAX(CASE WHEN p.rn = 1 THEN p.Name END) AS 'Product 1 name',
    MAX(CASE WHEN p.rn = 2 THEN p.Name END) AS 'Product 2 name',
    MAX(CASE WHEN p.rn = 1 THEN p.Value END) AS 'Product 1 value',
    MAX(CASE WHEN p.rn = 2 THEN p.Value END) AS 'Product 2 value'
FROM `ORDER` o -- ORDER是SQL保留关键字，建议加转义符避免报错
LEFT JOIN (
    SELECT 
        *,
        -- 按订单分组，给同订单下的产品生成1、2的序号
        ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY ProductId) rn
    FROM PRODUCT
) p ON o.OrderId = p.OrderId
GROUP BY o.OrderNo, o.OrderValue

方案2：两次LEFT JOIN（兼容所有数据库版本，包括老版本MySQL 5.x等） #

直接给产品表做两次关联，分别取第一个和第二个产品：

SELECT 
    o.OrderNo AS 'Order no',
    o.OrderValue AS 'Order value',
    p1.Name AS 'Product 1 name',
    p2.Name AS 'Product 2 name',
    p1.Value AS 'Product 1 value',
    p2.Value AS 'Product 2 value'
FROM `ORDER` o
LEFT JOIN PRODUCT p1 ON o.OrderId = p1.OrderId 
    -- 取当前订单下的第一个产品
    AND p1.ProductId = (SELECT MIN(ProductId) FROM PRODUCT WHERE OrderId = o.OrderId)
LEFT JOIN PRODUCT p2 ON o.OrderId = p2.OrderId 
    -- 取当前订单下的第二个产品
    AND p2.ProductId = (SELECT MAX(ProductId) FROM PRODUCT WHERE OrderId = o.OrderId)
-- 去重避免单产品订单出现重复行
GROUP BY o.OrderNo, o.OrderValue, p1.Name, p2.Name, p1.Value, p2.Value

注意事项 #

• 你原有查询只返回第一个产品，是因为原有查询结果为行式存储，一个订单关联两个产品会返回两行，你只取了第一行。上述两种方案都会把同订单的两个产品合并为一行，完全匹配你要的报表字段格式。
• 如果你的Product表有更明确的产品排序规则，可以替换上述SQL中的ORDER BY ProductId/MIN(ProductId)/MAX(ProductId)为你需要的排序字段，比如按产品添加时间排序等。

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