如何在SQL中添加第二价格列？原数据分两行存储

需要从RS_PCL表中查询Priceline为"J"的特殊客户售价，同时新增一列展示对应SKU和相同起始日期下Priceline为"R"的零售价（避免生成单独行）。尝试以下操作时遇到错误：

• 使用WHERE RS_PCL.PRICELINE IN ("J","R")会生成两行数据，不符合需求。
• 使用IIF函数查询时，提示**"查询未将表达式'GL Type'作为聚合函数的一部分"**，对应代码：

SELECT RS_PCL.[GL Type],
 RS_PCL.SKU,
 RS_PCL.[SKU Desc],
 RS_PCL.Supplier,
 RS_PCL.[Case UPC],
 RS_PCL.[Pack UPC],
 RS_PCL.[Unit UPC],
 RS_PCL.PDCN,
 RS_PCL.Package,
 RS_PCL.[Price Start Date],
 RS_PCL.[Price End Date],
    Iif( RS_PCL.PRICELINE = "J" , RS_PCL.Price , Null) AS [Sell Price],
    Iif( RS_PCL.PRICELINE = "R" , RS_PCL.Price , Null) AS [Retail Price],
 RS_PCL.Cost,
 RS_PCL.Tax,
 RS_PCL.Freight
FROM RS_PCL
WHERE (RS_PCL.PRICELINE IN ("J","R"))

• 测试自连接时出现**"FROM子句语法错误"**，对应代码：

SELECT J.*, R.R_PRICE FROM RS_PCL AS J
INNER JOIN SELECT (RS_PCL.SKU, RS_PCL.[Price Start Date], RS_PCL.Price AS R_PRICE FROM RS_PCL WHERE RS_PCL.PRICELINE = "R") AS R
ON J.SKU = R.SKU AND J.[Price Start Date] = R.[Price Start Date]
WHERE RS_PCL.Priceline = "J"

方法1：CASE WHEN + GROUP BY（推荐） #

错误原因：原IIF查询未做分组，同一SKU+起始日期会返回两行（J和R各一行），需要通过GROUP BY聚合提取对应价格。

修正后代码：

SELECT 
    RS_PCL.[GL Type],
    RS_PCL.SKU,
    RS_PCL.[SKU Desc],
    RS_PCL.Supplier,
    RS_PCL.[Case UPC],
    RS_PCL.[Pack UPC],
    RS_PCL.[Unit UPC],
    RS_PCL.PDCN,
    RS_PCL.Package,
    RS_PCL.[Price Start Date],
    RS_PCL.[Price End Date],
    MAX(CASE WHEN RS_PCL.PRICELINE = 'J' THEN RS_PCL.Price END) AS [Sell Price],
    MAX(CASE WHEN RS_PCL.PRICELINE = 'R' THEN RS_PCL.Price END) AS [Retail Price],
    MAX(RS_PCL.Cost) AS Cost,
    MAX(RS_PCL.Tax) AS Tax,
    MAX(RS_PCL.Freight) AS Freight
FROM RS_PCL
WHERE RS_PCL.PRICELINE IN ('J','R')
GROUP BY 
    RS_PCL.[GL Type],
    RS_PCL.SKU,
    RS_PCL.[SKU Desc],
    RS_PCL.Supplier,
    RS_PCL.[Case UPC],
    RS_PCL.[Pack UPC],
    RS_PCL.[Unit UPC],
    RS_PCL.PDCN,
    RS_PCL.Package,
    RS_PCL.[Price Start Date],
    RS_PCL.[Price End Date]

说明：

• 用CASE WHEN匹配对应Priceline的价格，通过MAX聚合（同一分组下只有一行对应J/R，MAX会取到非空值）
• 所有非聚合字段必须加入GROUP BY子句，解决"未作为聚合函数一部分"的错误

方法2：修正自连接语法 #

错误原因：子查询缺少外层括号，且WHERE子句引用了未别名的原表名。

修正后代码：

SELECT 
    J.[GL Type],
    J.SKU,
    J.[SKU Desc],
    J.Supplier,
    J.[Case UPC],
    J.[Pack UPC],
    J.[Unit UPC],
    J.PDCN,
    J.Package,
    J.[Price Start Date],
    J.[Price End Date],
    J.Price AS [Sell Price],
    R.Price AS [Retail Price],
    J.Cost,
    J.Tax,
    J.Freight
FROM RS_PCL AS J
INNER JOIN (
    SELECT SKU, [Price Start Date], Price 
    FROM RS_PCL 
    WHERE PRICELINE = 'R'
) AS R ON J.SKU = R.SKU AND J.[Price Start Date] = R.[Price Start Date]
WHERE J.PRICELINE = 'J'

说明：

• 子查询必须用括号包裹，表别名R的定义需规范
• WHERE子句使用表别名J过滤Priceline，避免歧义
• 若存在部分SKU无对应R价格的情况，可将INNER JOIN改为LEFT JOIN，此时无R价格的行[Retail Price]会显示为NULL

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