☰

MS SQL 查询子查询的总和

tag-icon • tag-icon sql-server query
MS SQL 查询子查询的总和

我需要帮助获取查询的以下输出。

SELECT
      ARG_CONSUMER,
      cast(ARG_TOTALAMT as float)/100 AS 'Total',
      (SELECT SUM(cast(DAMT as float))/100 FROM DEBT WHERE DDATE >= ARG.ARG_ORIGDATE AND DDATE <= ARG.ARG_LASTPAYDATE AND DTYPE IN ('CSH','CNTP','DDR','NBP') AND DCONSUMER = ARG.ARG_CONSUMER
        ) AS 'Paid'
FROM
     ARGMASTER ARG
WHERE ARG_STATUS = '1'

当前输出是所有记录的列表......

但我想在这里实现的是

count of arg consumers
Total of ARG_TOTALAMT
total of that subquery  PAID
difference between PAID & Total amount.

我可以实现前两个，即消费者数量和 ARG _ TOTALAMT 总数...但我对总和感到困惑...即

sum (SELECT SUM(cast(DAMT as float))/100 FROM DEBT WHERE DDATE >= ARG.ARG_ORIGDATE AND DDATE <= ARG.ARG_LASTPAYDATE AND DTYPE IN ('CSH','CNTP','DDR','NBP') AND DCONSUMER = ARG.ARG_CONSUMER) AS 'Paid'

请指教

答案1

将子查询更改为 OUTER APPLY

SELECT
      COUNT(DISTINCT ARG_CONSUMER) AS [count of arg consumers],
      cast(ARG_TOTALAMT as float)/100 AS [Total],
      foo.Paid,
      cast(ARG_TOTALAMT as float)/100 - foo.Paid AS [difference between PAID & Total amount]
FROM
     ARGMASTER ARG
     OUTER APPLY
     (
     SELECT
         SUM(cast(D.DAMT as float))/100 AS Paid
     FROM DEBT D
     WHERE
          DDATE >= ARG.ARG_ORIGDATE AND D.DDATE <= ARG.ARG_LASTPAYDATE
          ANDD. DTYPE IN ('CSH','CNTP','DDR','NBP') AND D.DCONSUMER = ARG.ARG_CONSUMER
     ) foo
WHERE ARG.ARG_STATUS = '1'

相关内容