在 MSSQL-SERVER 中計算 Settele 和提取金額的 T-SQL 查詢

我有一個具有以下結構的表：

FactSatnaEvent(OPRDATE date,OPRCOD int, voucherno int, SATNAPAYATYPE int, CFCIFNO int , AMOUNT int)

基於這張表我寫了這個查詢

      SELECT 
         S.OPRDATE ,
         S.CFCIFNO ,
         SUM(CASE WHEN S.SATNAPAYATYPE=1 THEN S.AMOUNT ELSE 0 END) AS SATNA_DEPOSIT_AMNT,
         SUM(CASE WHEN S.SATNAPAYATYPE=2 AND 
                       S.OPRCOD=0 AND 
                       S.voucherno<>-1 THEN S.AMOUNT ELSE 0 END) AS SATNA_WITHDRAW_AMNT,

         COUNT(CASE WHEN S.SATNAPAYATYPE=1 THEN 1 ELSE NULL END) AS SATNA_DEPOSIT_COUNT,
         COUNT(CASE WHEN S.SATNAPAYATYPE=2 AND 
                         S.OPRCOD=0 AND 
                         S.voucherno<>-1 THEN 1 ELSE NULL END)   AS SATNA_WITHDRAW_COUNT
  from factsatnaevent s
  where s.cfcifno<>-1 
  GROUP BY S.OPRDATE ,
           S.CFCIFNO ;

我只想知道是否有更好的方法來編寫這個查詢。我需要有這個輸出

OPRDATE   CFCIFNO    SATNA_DEPOSIT_AMNT    SATNA_WITHDRAW_AMNT     SATNA_DEPOSIT_COUNT   SATNA_WITHDRAW_COUNT

請告知是否需要任何進一步的資訊。提前致謝

1. 避免<>在 where 條件下使用。而是使用>-1
2. 當您已經過濾了類似的內容 where s.cfcifno<>-1 時，則無需放入相同的內容，case condition因為該條件已被過濾。

你的查詢對我來說似乎沒問題，

SELECT 
         S.OPRDATE ,
         S.CFCIFNO ,
         SUM(CASE WHEN S.SATNAPAYATYPE=1 THEN S.AMOUNT ELSE 0 END) AS SATNA_DEPOSIT_AMNT,
         SUM(CASE WHEN S.SATNAPAYATYPE=2 AND 
                       S.OPRCOD=0  
                       THEN S.AMOUNT ELSE 0 END) AS SATNA_WITHDRAW_AMNT,

         COUNT(CASE WHEN S.SATNAPAYATYPE=1 THEN 1 ELSE NULL END) AS SATNA_DEPOSIT_COUNT,
         COUNT(CASE WHEN S.SATNAPAYATYPE=2 AND 
                         S.OPRCOD=0  
                         THEN 1 ELSE NULL END)   AS SATNA_WITHDRAW_COUNT
  from factsatnaevent s
  where s.cfcifno>-1 
  GROUP BY S.OPRDATE ,
           S.CFCIFNO ;

您是否面臨任何性能問題？

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