>我有 2 个子查询
SELECT
SUM(QTY_BOUGHT * PRICE),
SUM(QTY_BOUGHT),
SUM(QTY_BOUGHT * PRICE) / SUM(QTY_BOUGHT)
FROM
TABLE_A
WHERE
DAYS_AGED >= 792
和
SELECT
SUM(QTY_BOUGHT * PRICE),
SUM(QTY_BOUGHT),
SUM(QTY_BOUGHT * PRICE) / SUM(QTY_BOUGHT)
FROM
TABLE_A
WHERE
DAYS_AGED < 792
问题是我想使用简单的案例语句将它们组合为:
SELECT
CASE
WHEN DAYS_AGED >= 792
THEN SUM(QTY_BOUGHT * PRICE) / SUM(QTY_BOUGHT)
WHEN DAYS_AGED < 792
THEN SUM(QTY_BOUGHT * PRICE) / SUM(QTY_BOUGHT)
END
FROM
TABLE_A
我收到此错误:
Msg 8120,级别 16,状态 1,第 127 行
列"TABLE_A.DAYS_AGED"在选择列表中无效,因为它未包含在聚合函数或 GROUP BY 子句中。
我可能想补充一点,我可以使用 UNION 或 CTE 将它们组合起来,这行得通
SELECT
SUM(QTY_BOUGHT * PRICE),
SUM(QTY_BOUGHT),
SUM(QTY_BOUGHT * PRICE) / SUM(QTY_BOUGHT)
FROM
TABLE_A
WHERE
DAYS_AGED >= 792
UNION
SELECT
SUM(QTY_BOUGHT * PRICE),
SUM(QTY_BOUGHT),
SUM(QTY_BOUGHT * PRICE) / SUM(QTY_BOUGHT)
FROM
TABLE_A
WHERE
DAYS_AGED < 792
有没有办法使用 CASE 语句整理这两个子查询,有没有可能这样做
您需要将CASE
表达式放在聚合函数中:
SELECT
SUM(CASE WHEN DAYS_AGED >= 792 THEN QTY_BOUGHT*PRICE END) amount1,
SUM(CASE WHEN DAYS_AGED >= 792 THEN QTY_BOUGHT END) qty_bought1,
SUM(CASE WHEN DAYS_AGED >= 792 THEN QTY_BOUGHT * PRICE END)
/ SUM(CASE WHEN DAYS_AGED >= 792 THEN QTY_BOUGHT END) avg1,
SUM(CASE WHEN DAYS_AGED < 792 THEN QTY_BOUGHT*PRICE END) amount2,
SUM(CASE WHEN DAYS_AGED < 792 THEN QTY_BOUGHT END) qty_bought2,
SUM(CASE WHEN DAYS_AGED < 792 THEN QTY_BOUGHT * PRICE END)
/ SUM(CASE WHEN DAYS_AGED < 792 THEN QTY_BOUGHT END) avg2
FROM TABLE_A