Oracle SQL查询优化：将产品关联日期与包装转为列而非行

你当前的左连接会因为Dates和Packs表的多对一关系产生笛卡尔积，要实现单条产品对应多列日期/包装的效果，可按以下方式处理：

通用解决方案（兼容多数SQL数据库） #

通过窗口函数编号+条件聚合的方式，先给每个产品的日期、包装条目按顺序编号，再将同编号的条目转为独立列：

SELECT
  p.id,
  p.product,
  -- 生成日期列
  MAX(CASE WHEN d.rn = 1 THEN d.date END) AS date_1,
  MAX(CASE WHEN d.rn = 2 THEN d.date END) AS date_2,
  MAX(CASE WHEN d.rn = 3 THEN d.date END) AS date_3,
  -- 生成包装列
  MAX(CASE WHEN pc.rn = 1 THEN pc.pack END) AS pack_1,
  MAX(CASE WHEN pc.rn = 2 THEN pc.pack END) AS pack_2,
  MAX(CASE WHEN pc.rn = 3 THEN pc.pack END) AS pack_3
FROM Products p
LEFT JOIN (
  -- 给每个产品的日期按顺序编号
  SELECT 
    id,
    date,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) AS rn
  FROM Dates
) d ON p.id = d.id
LEFT JOIN (
  -- 给每个产品的包装按顺序编号
  SELECT 
    id,
    pack,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY pack) AS rn
  FROM Packs
) pc ON p.id = pc.id
GROUP BY p.id, p.product
ORDER BY p.id;

关键逻辑说明 #

1. 子查询中用ROW_NUMBER()给同一产品的日期/包装条目分配唯一序号，确保每个值对应固定列。
2. 外层用MAX(CASE ...)过滤NULL值，把同序号的条目聚合到对应列（因为每个序号只有一条有效数据，其他序号的CASE结果为NULL，MAX会自动保留有效值）。
3. 如果产品的日期/包装数量超过3个，继续添加MAX(CASE WHEN rn = N THEN ...)格式的列即可。

特定数据库简化写法 #

PostgreSQL（使用crosstab函数） #

先启用tablefunc扩展，再用crosstab快速转置：

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT
  p.id,
  p.product,
  d.date_1,
  d.date_2,
  d.date_3,
  pc.pack_1,
  pc.pack_2,
  pc.pack_3
FROM Products p
LEFT JOIN crosstab(
  'SELECT id, rn, date FROM (
     SELECT id, date, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) AS rn FROM Dates
   ) t ORDER BY 1,2'
) d(id INT, date_1 DATE, date_2 DATE, date_3 DATE) ON p.id = d.id
LEFT JOIN crosstab(
  'SELECT id, rn, pack FROM (
     SELECT id, pack, ROW_NUMBER() OVER (PARTITION BY id ORDER BY pack) AS rn FROM Packs
   ) t ORDER BY 1,2'
) pc(id INT, pack_1 INT, pack_2 INT, pack_3 INT) ON p.id = pc.id
ORDER BY p.id;

SQL Server（使用PIVOT运算符） #

借助原生PIVOT实现转置：

WITH DateCTE AS (
  SELECT 
    id,
    date,
    'date_' + CAST(ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) AS VARCHAR) AS col_name
  FROM Dates
),
PackCTE AS (
  SELECT 
    id,
    pack,
    'pack_' + CAST(ROW_NUMBER() OVER (PARTITION BY id ORDER BY pack) AS VARCHAR) AS col_name
  FROM Packs
),
DatePivot AS (
  SELECT * FROM DateCTE PIVOT (MAX(date) FOR col_name IN ([date_1], [date_2], [date_3])) AS pvt
),
PackPivot AS (
  SELECT * FROM PackCTE PIVOT (MAX(pack) FOR col_name IN ([pack_1], [pack_2], [pack_3])) AS pvt
)
SELECT
  p.id,
  p.product,
  dp.date_1,
  dp.date_2,
  dp.date_3,
  pp.pack_1,
  pp.pack_2,
  pp.pack_3
FROM Products p
LEFT JOIN DatePivot dp ON p.id = dp.id
LEFT JOIN PackPivot pp ON p.id = pp.id
ORDER BY p.id;

最终结果示例 #

执行后会得到符合需求的横向结果：

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