如何从同一列添加正负合计

您可以在此处使用条件聚合:

SELECT
    SUM(CASE WHEN FINAL_OVER_SHORT_AMOUNT >= 0
             THEN FINAL_OVER_SHORT_AMOUNT ELSE 0 END) AS pos_amount,
    SUM(CASE WHEN FINAL_OVER_SHORT_AMOUNT < 0
             THEN FINAL_OVER_SHORT_AMOUNT ELSE 0 END) AS neg_amount
FROM dbo.over_short;

您还可以使用类似的查询 GROUP BY ,假设您需要给定组/列的正/负和,例如 ID 列。

Sign 是一个方便的小功能:

-- Sample data.
declare @Samples as Table ( Sample Decimal(10,2) );
insert into @Samples ( Sample ) values
  ( -10.00 ), ( -5.00 ), ( 0.00 ), ( 42.00 ), ( 8.00 );

-- Demonstrate separating values by sign.
select Sample,
  Sample * -Sign( Sign( Sample ) - 1 ) as Negative,
  Sample * Sign( Sign( Sample ) + 1 ) as Positive
  from @Samples;

-- Calculate the sums.
select Sum( Sample ) as Total,
  Sum( Sample * -Sign( Sign( Sample ) - 1 ) ) as NegativeTotal,
  Sum( Sample * Sign( Sign( Sample ) + 1 ) ) as PositiveTotal
  from @Samples;

你可以用这个
select sum(case when FINAL_OVER_SHORT_AMOUNT>0 then FINAL_OVER_SHORT_AMOUNT else 0 end)as Positive, sum(case when FINAL_OVER_SHORT_AMOUNT<0 then FINAL_OVER_SHORT_AMOUNT else 0 end) as Negative from over_short
或 您可以将存储结果分离到临时表中并使用它
select sum(case when FINAL_OVER_SHORT_AMOUNT>0 then FINAL_OVER_SHORT_AMOUNT else 0 end)as Positive, sum(case when FINAL_OVER_SHORT_AMOUNT<0 then FINAL_OVER_SHORT_AMOUNT else 0 end) as Negative into #Temp from [dbo].[Temp]

选择 和(正)为正和,和(负)为负 从温度