在sql 2012的update语句中使用sum时发生聚合错误

aor9mmx1 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(363)

我有以下问题。当我执行它时，我得到了这个错误。你能帮我理解一下，我还能怎么和sum（收入）做比较吗。
聚合不能出现在update语句的集合列表中。

WITH CTE AS(
    SELECT DIV,
           PRODUCT,
           CUSTOMER,
           REVENUE,
           REVENUE_TYPE,
           MAX(REVENUE) OVER (PARTITION BY PRODUCT,CUSTOMER) AS MAX_REVENUE,
    FROM TABLE)
UPDATE CTE
SET REVENUE_TYPE = CASE 
                            WHEN REVENUE = MAX_REVENUE                        THEN 'High'
                            WHEN (MAX_REVENUE-REVENUE) < (0.1 * SUM(REVENUE)) THEN 'High'
                            ELSE 'Low'
                            END

提前谢谢！

sql sql-server

来源：https://stackoverflow.com/questions/62551348/error-on-aggregation-when-using-sum-in-update-statement-in-sql-2012

2条答案

按热度按时间

cbwuti441#

您还应该计算cte中的总和

WITH CTE AS(
    SELECT DIV,
           PRODUCT,
           CUSTOMER,
           REVENUE,
           REVENUE_TYPE,
           MAX(REVENUE) OVER (PARTITION BY PRODUCT,CUSTOMER) AS MAX_REVENUE,
           SUM(REVENUE) OVER (PARTITION BY PRODUCT,CUSTOMER) AS SUM_REVENUE
    FROM TABLE)
UPDATE CTE
SET REVENUE_TYPE = CASE WHEN REVENUE = MAX_REVENUE                       THEN 'High'
                        WHEN (MAX_REVENUE-REVENUE) < (0.1 * SUM_REVENUE) THEN 'High'
                        ELSE 'Low'
                   END

赞(0）回复(0）举报 2021-07-24

wgx48brx2#

您可以在cte中添加另一列 REVENUE 并在update语句中使用它：

WITH CTE AS(
    SELECT DIV,
           PRODUCT,
           CUSTOMER,
           REVENUE,
           REVENUE_TYPE,
           MAX(REVENUE) OVER (PARTITION BY PRODUCT, CUSTOMER) AS MAX_REVENUE,
           SUM(REVENUE) OVER (PARTITION BY PRODUCT, CUSTOMER) AS SUM_REVENUE
    FROM TABLE
)
UPDATE CTE
SET REVENUE_TYPE = CASE 
  WHEN (REVENUE = MAX_REVENUE) OR ((MAX_REVENUE - REVENUE) < (0.1 * SUM_REVENUE)) THEN 'High'
  ELSE 'Low'
END

赞(0）回复(0）举报 2021-07-24

我来回答

在sql 2012的update语句中使用sum时发生聚合错误

2条答案

相关问题

热门标签

最新问答