如何实现产品数量超3时换行展示员工及产品明细

需求说明 #

需要展示员工的名（firstName）、姓（lastName）及产品明细，当产品数量超过3个时，剩余产品需换行展示在与前3个产品对应的列中（即每行最多显示3个产品）。

示例数据 #

员工名: Emma
员工姓: Snow
产品1: Apple
产品1数量: 100
产品2: Banana
产品2数量: 50
产品3: Guava
产品3数量: 40
产品4: Watermelon
产品4数量: 60
产品5: Melon
产品5数量: 30

预期结果 #

employeefname employeelname product1   product1amount product2 product2amount product3 product3amount
------------- ------------- ---------- -------------- -------- -------------- -------- --------------
Emma          Snow          Apple      100            Banana   50             Guava    40
Emma          Snow          Watermelon 60             Melon    30

现有SQL问题分析及调整方案 #

你当前的SQL存在几个核心问题：

1. 从DUAL表的子查询无法获取当前行的产品名称和数量，逻辑完全错误；
2. 多余的PIVOT操作与需求无关，需求不需要按费用用途分组；
3. 分组逻辑错误，没有实现每3个产品为一行的分组规则。

以下是调整后的SQL代码，通过先给员工的产品分组、再转列的方式实现需求：

WITH employee_products AS (
    SELECT 
        rpt.employee_id,
        prd.product_name,
        ROUND(rpt.amount) AS product_amount, -- 保留原SQL的ROUND处理
        -- 计算每3个产品为一组的行编号
        CEIL(ROW_NUMBER() OVER (PARTITION BY rpt.employee_id ORDER BY rpt.product_id) / 3) AS row_group,
        -- 组内产品的序号（1-3循环）
        MOD(ROW_NUMBER() OVER (PARTITION BY rpt.employee_id ORDER BY rpt.product_id) - 1, 3) + 1 AS item_seq
    FROM state_customer_expense rpt
    JOIN product prd ON rpt.product_id = prd.product_id
    WHERE rpt.state_period_id = 10001001210627
)
SELECT 
    emp.first_name AS employeefname,
    emp.last_name AS employeelname,
    MAX(CASE WHEN item_seq = 1 THEN product_name END) AS product1,
    MAX(CASE WHEN item_seq = 1 THEN product_amount END) AS product1amount,
    MAX(CASE WHEN item_seq = 2 THEN product_name END) AS product2,
    MAX(CASE WHEN item_seq = 2 THEN product_amount END) AS product2amount,
    MAX(CASE WHEN item_seq = 3 THEN product_name END) AS product3,
    MAX(CASE WHEN item_seq = 3 THEN product_amount END) AS product3amount
FROM employee emp
JOIN employee_products ep ON emp.employee_id = ep.employee_id
GROUP BY emp.employee_id, emp.first_name, emp.last_name, ep.row_group
ORDER BY emp.employee_id, ep.row_group;

代码说明 #

1. CTE部分：employee_products先给每个员工的产品按顺序编号，通过CEIL(ROW_NUMBER()/3)把每3个产品分到同一行组，用MOD(ROW_NUMBER()-1,3)+1生成组内的1-3序号；
2. 主查询部分：通过CASE WHEN配合MAX聚合，把组内序号为1、2、3的产品分别映射到对应的列，最后按员工和行组分组，实现每行最多3个产品的展示效果；
3. 保留了原SQL中的ROUND处理产品数量，同时去掉了多余的DISTINCT和无关的PIVOT逻辑。

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