如何基于SKU将分层定价表拆分为含唯一行的多张表？

步骤1：为每个SKU的定价行标记层级 #

利用ROW_NUMBER()窗口函数，按SKU分组，再按购买量（qty）降序排序，为每个SKU的定价行分配层级编号——购买量最大的定价标记为层级1，次大的为层级2，以此类推：

SELECT 
    sku,
    qty,
    price,
    ROW_NUMBER() OVER (PARTITION BY sku ORDER BY qty DESC) AS tier
FROM pricing_table;

步骤2：按层级筛选并导出CSV #

针对每个层级（1到4），筛选对应数据并导出为独立CSV文件，使用MySQL的INTO OUTFILE语句完成导出：

导出层级1的CSV：

SELECT sku, qty, price
FROM (
    SELECT 
        sku,
        qty,
        price,
        ROW_NUMBER() OVER (PARTITION BY sku ORDER BY qty DESC) AS tier
    FROM pricing_table
) AS tiered_data
WHERE tier = 1
INTO OUTFILE '/your/output/path/tier_1.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

导出层级2的CSV：

SELECT sku, qty, price
FROM (
    SELECT 
        sku,
        qty,
        price,
        ROW_NUMBER() OVER (PARTITION BY sku ORDER BY qty DESC) AS tier
    FROM pricing_table
) AS tiered_data
WHERE tier = 2
INTO OUTFILE '/your/output/path/tier_2.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

导出层级3的CSV：

SELECT sku, qty, price
FROM (
    SELECT 
        sku,
        qty,
        price,
        ROW_NUMBER() OVER (PARTITION BY sku ORDER BY qty DESC) AS tier
    FROM pricing_table
) AS tiered_data
WHERE tier = 3
INTO OUTFILE '/your/output/path/tier_3.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

导出层级4的CSV：

SELECT sku, qty, price
FROM (
    SELECT 
        sku,
        qty,
        price,
        ROW_NUMBER() OVER (PARTITION BY sku ORDER BY qty DESC) AS tier
    FROM pricing_table
) AS tiered_data
WHERE tier = 4
INTO OUTFILE '/your/output/path/tier_4.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

关键说明 #

• PARTITION BY sku是核心逻辑：确保层级编号在每个SKU内部独立计算，而非全局统一编号，这是你之前使用ROW_NUMBER()时可能缺失的部分。
• 按qty DESC排序匹配“购买量越大价格越优惠”的规则，保证最大采购量对应最高层级。
• 若SKU定价层级不足4（如示例中的abc只有3层），对应高层级CSV中不会出现该SKU数据，完全符合你期望的输出结果。

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