如果至少為負數，求和；否則只顯示

僅當分區中存在負值時，我才需要將值相加。如果分區中沒有負值，它應該只輸出該行。

這就是我現在所擁有的。初始數據作為 CTE 提供。

DML

;with ledger as (
   select accountId, type, amount
   from (
       values
        (1, 'R', -10)
       ,(1, 'V', 10)
       ,(1, 'R', 30)
       ,(2, 'R', 20)
       ,(2, 'R', -5)
       ,(2, 'V', 5)
       ,(3, 'R', 20)
       ,(3, 'R', 30)
   )   x (accountId, type, amount)
)
,b as ( --identifies accountid, type with negatives
   select
       accountid
       ,type               
   from ledger
   group by accountid, type
   having min(amount) < 0
)
,onlyPositives as (
   select
        l.accountid
       ,l.type
       ,l.amount
   from ledger l
   left join b on b.accountid = l.accountid
               and b.type = l.type
   where b.accountid is null
)
,aggregatedNegatives as (
   select 
        l.accountid
       ,l.type
       ,amount = sum(l.amount)
   from ledger l
   inner join b on b.accountid = l.accountid
               and b.type = l.type 
   group by l.accountid, l.type
)
select  accountid, type, amount
from    onlyPositives

union all

select  accountid, type, amount
from    aggregatedNegatives

我期待這樣的輸出，並且上面的查詢輸出正確。

1, R, 20  (summed because -10+30=20)
1, V, 10  (left alone)
2, R, 15  (summed because 20-5=15)
2, V, 5   (left alone)
3, R, 20  (left alone)
3, R, 30  (left alone)

到目前為止，該查詢看起來很糟糕，並且感覺不必要地複雜。是否有一個我可以寫但我忽略的更簡單的查詢？

這是該問題的一個細微變化：如果是肯定的，則將所有項目相加。如果為負，則返回每一個

rextester -> https://rextester.com/EZRT33825

您可以在使用視窗函式後完成您的工作，但如果您在很多行上執行此查詢，我無法對性能做出任何承諾。這個想法是計算每個分區的總和、最小值和無序行數。保留最小值 > 0 的所有行，但僅在最小值 < 0 時保留分區的第一行。

-- put data into temp table for illustration purposes
select accountId, type, amount into #t220618
from (
   values
    (1, 'R', -10)
   ,(1, 'R', 30)
   ,(1, 'V', 10)        
   ,(2, 'R', 20)
   ,(2, 'R', -5)
   ,(2, 'V', 5)
   ,(3, 'R', 20)
   ,(3, 'R', 30)
)   x (accountId, type, amount);


SELECT
 accountId
, type
, CASE WHEN part_min < 0 THEN part_sum else amount END amount
FROM (
   SELECT
     accountId
   , type
   , amount
   , SUM(amount) OVER (PARTITION BY accountId, type) part_sum
   , MIN(amount) OVER (PARTITION BY accountId, type) part_min
   , ROW_NUMBER() OVER (PARTITION BY accountId, type ORDER BY (SELECT NULL)) part_rn
   FROM #t220618
) q
WHERE q.part_min > 0 OR (part_min < 0 AND part_rn = 1);

引用自：https://dba.stackexchange.com/questions/220618