SQL实现行内多列自定义分类逻辑的高效方案咨询

需求说明 #

需要将transactions表中的交易记录，按照交易类型（TYPE）和金额列（revenue/shipping/rebates）拆分，生成符合会计分类要求的明细记录：每条记录仅保留一个金额列的值，其余列置空，并根据TYPE和对应列分配唯一的会计科目编码（accounting_code）。现有基于多次子查询+UNION的方案性能低下，需优化。

现有表结构与示例数据 #

transactions表结构及数据如下：

预期结果 #

拆分后的会计分类明细记录示例（以订单100001为例）：

现有低效方案 #

现有方案通过创建临时表，对每个金额列单独查询（其余列置0），再用UNION合并结果，多次扫描原表且语法繁琐，性能差。示例代码如下：

WITH TT AS (SELECT * FROM transactions)
SELECT 
  TYPE,
  ORDER_no,
  SUM(revenue) AS revenue,
  0 AS shipping,
  0 AS rebates,
  CONCAT(type,' | ', 'revenue') AS acc_code
FROM TT
GROUP BY TYPE, Order_no, CONCAT(type,' | ', 'revenue') 

UNION

-- 重复上述逻辑处理shipping列
SELECT 
  TYPE,
  ORDER_no,
  0 AS revenue,
  SUM(shipping) AS shipping,
  0 AS rebates,
  CONCAT(type,' | ', 'shipping') AS acc_code
FROM TT
GROUP BY TYPE, Order_no, CONCAT(type,' | ', 'shipping') 

UNION

-- 重复上述逻辑处理rebates列
SELECT 
  TYPE,
  ORDER_no,
  0 AS revenue,
  0 AS shipping,
  SUM(rebates) AS rebates,
  CONCAT(type,' | ', 'rebates') AS acc_code
FROM TT
GROUP BY TYPE, Order_no, CONCAT(type,' | ', 'rebates') 

优化方案：单次扫描+横向拆分 #

核心思路是仅扫描一次原表，通过横向连接将每行拆分为对应金额列的多条记录，避免多次扫描和UNION的性能开销。

通用SQL实现（兼容多数数据库） #

SELECT
  t.TYPE,
  t.ORDER_NO,
  -- 根据拆分的列名填充对应金额，其余列置空
  CASE WHEN split.col = 'revenue' THEN t.revenue ELSE NULL END AS revenue,
  CASE WHEN split.col = 'shipping' THEN t.shipping ELSE NULL END AS shipping,
  CASE WHEN split.col = 'rebates' THEN t.rebates ELSE NULL END AS rebates,
  -- 根据TYPE和列名映射会计科目编码
  CASE
    WHEN t.TYPE = 'SALE' AND split.col = 'revenue' THEN '101'
    WHEN t.TYPE = 'SALE' AND split.col = 'shipping' THEN '102'
    WHEN t.TYPE = 'SALE' AND split.col = 'rebates' THEN '103'
    WHEN t.TYPE = 'REFUND' AND split.col = 'revenue' THEN '104'
    WHEN t.TYPE = 'REFUND' AND split.col = 'rebates' THEN '105'
    WHEN t.TYPE = 'ADJUSTMENT' AND split.col = 'revenue' THEN '106'
    WHEN t.TYPE = 'ADJUSTMENT' AND split.col = 'shipping' THEN '107'
    -- 可扩展更多TYPE和列的映射规则
  END AS accounting_code
FROM transactions t
-- 生成需要拆分的列名列表，将每行拆分为3条记录
CROSS JOIN (
  VALUES ('revenue'), ('shipping'), ('rebates')
) AS split(col)
-- 过滤掉金额为0的记录（根据需求可选）
WHERE 
  (split.col = 'revenue' AND t.revenue != '$0')
  OR (split.col = 'shipping' AND t.shipping != '$0')
  OR (split.col = 'rebates' AND t.rebates != '$0');

性能优势说明 #

1. 单次表扫描：仅读取一次transactions表，避免了UNION方案中多次扫描的IO开销。
2. 简洁可维护：新增金额列或会计规则时，只需修改VALUES列表和CASE语句，无需重复编写子查询。
3. 避免额外排序：UNION会自动去重排序，而此方案无需额外排序，性能更优。

数据库特定优化 #

• SQL Server/Azure SQL：可使用CROSS APPLY替代CROSS JOIN VALUES，语法更贴合数据库特性。
• PostgreSQL：可使用UNNEST(ARRAY['revenue','shipping','rebates']) AS split(col)实现拆分。
• MySQL 8.0+：支持VALUES子句和CROSS JOIN，直接使用上述通用方案即可。

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