sql-server - 在开窗函数中使用 Case ( OVER (Partition))

Question

我正在努力实现以下目标:

SELECT 
   *,
   CASE WHEN 1 THEN SUM(b.myField) ELSE AVG(b.myField) END OVER (PARTITION BY ID)
FROM tbl a
LEFT JOIN tbl2 b ON a.ID = b.aID

这可以通过 SQL Server 中的窗口函数实现吗？

我能够在没有 case 语句的情况下完成以下任务:

SELECT 
   *,
   SUM(b.myField) OVER (PARTITION BY ID)
FROM tbl a
LEFT JOIN tbl2 b ON a.ID = b.aID

Answer 1

你可以试试这个:

SELECT 
   *,
   CASE WHEN (SUM(b.myField) 
OVER (PARTITION BY ID))=1 THEN SUM(b.myField)
 ELSE AVG(b.myField) END 
FROM tbl a
LEFT JOIN tbl2 b ON a.ID = b.aID