如何在SQLServer2008中根據百分比求和和減去一列值

DECLARE @BalanceTblRec TABLE 
                       (
                           NetAmount decimal(18, 3), 
                           Percentage int, 
                           [Description] nvarchar(max)
                       )
DECLARE @BalanceTblPay TABLE 
                       (
                           NetAmount decimal(18, 3), 
                           Percentage int, 
                           [Description] nvarchar(max)
                       )

INSERT INTO @BalanceTblRec 
VALUES (21, 11, 'ReceiveReceipt'),
       (20, 11, 'ReceiveReceipt'),
       (20, 10, 'ReceiveReceipt'),
       (20, 20, 'ReceiveReceipt'), 
       (10, 10, 'ReceiveReceipt')

INSERT INTO @BalanceTblPay 
VALUES (10, 11, 'PayReceipt'),
       (10, 11, 'PayReceipt'), 
       (10, 2, 'PayReceipt'),
       (5, 15, 'PayReceipt'),
       (30, 10, 'PayReceipt'),
       (20, 10, 'PayReceipt')

;WITH MaPercentage AS
( 
    SELECT
        Percentage, 
        SUM(NetAmount) AS Net, 
        'Receive' AS Flag 
    FROM
        @BalanceTblRec 
    GROUP BY
        Percentage
    UNION ALL
    SELECT 
        Percentage, 
        SUM(NetAmount) AS Net, 
        'Pay' AS Flag 
    FROM
        @BalanceTblPay 
    GROUP BY 
        Percentage
)
SELECT * FROM MaPercentage 

現在我想net從net基于falg，receive - pay基于百分比中減去。

像這樣：

Per Net         Flag
-----------------------
10  30.000 - 50 Receive
11  41.000 - 20 Receive
20  20.000      Receive
2   10.000      Pay
15  5.000       Pay

uj5u.com熱心網友回復：

我認為這就是你想要的：

DECLARE @BalanceTblRec TABLE (NetAmount decimal(18,3), Percentage int, [Description] nvarchar(max))
DECLARE @BalanceTblPay TABLE (NetAmount decimal(18,3), Percentage int, [Description] nvarchar(max))

insert into @BalanceTblRec values (21, 11, 'ReceiveReceipt'),(20, 11, 'ReceiveReceipt'),(20, 10, 'ReceiveReceipt'),(20, 20, 'ReceiveReceipt'), (10, 10, 'ReceiveReceipt')
insert into @BalanceTblPay values (10, 11, 'PayReceipt'),(10, 11, 'PayReceipt'),(10, 2, 'PayReceipt'),(5, 15, 'PayReceipt'),(30, 10, 'PayReceipt') ,(20, 10, 'PayReceipt')


;WITH MaPercentage as ( 
select Percentage, sum(NetAmount) as Net, 'Receive' as Flag from @BalanceTblRec group by Percentage
union all
select Percentage, -sum(NetAmount) as Net, 'Pay' as Flag from @BalanceTblPay group by Percentage
)

select 
  Percentage, 
  abs(sum(net)) as SumNet,
  case when sum(net) > 0 then 'Receive' 
  else 'Pay'
  end as Flag
from MaPercentage 
group by Percentage

剛剛更改了按百分比分組的薪酬和總和中的符號。

uj5u.com熱心網友回復：

另一種方法是完全加入收款和付款。

;WITH RCV AS (
  select Percentage, sum(NetAmount) as Net
  from @BalanceTblRec
  group by Percentage
)
, PAY AS (
  select Percentage, sum(NetAmount) as Net
  from @BalanceTblPay 
  group by Percentage
)
SELECT 
COALESCE(r.Percentage, p.Percentage) AS Percentage, 
ABS(COALESCE(r.Net, 0) - COALESCE(p.Net, 0)) AS Net, 
(CASE 
 WHEN (COALESCE(r.Net, 0) - COALESCE(p.Net, 0)) < 0 THEN 'Pay'
 ELSE 'Receive'
 END) AS Flag
FROM RCV r
FULL JOIN PAY p ON p.Percentage = r.Percentage

標籤：sql sql-server sql-server-2008

上一篇：某個時間范圍內來自RESTAPI的ADF請求

下一篇：Maven沒有運行JUnit測驗