Db2

如何添加我的子查詢的值，我想將 Cash、Charge 和 Other_Charges 的值添 加為 Total_payment

  • September 8, 2016

我正在使用 2 個表，TL_FISCALREPORT並且TL_TENDER. 我想要做的是添加我的 3 個子查詢的總值，TL_TENDER命名為現金、費用、投標，並在我的查詢中顯示：

SELECT DISTINCT F.DT_TIME,F.DT_DATE,'DEPT STORE' AS CATEGORY,
     F.STOREID,F.TID,(F.CURRENT_POSITIVE) AS NEW_GT,
     PREV_POSITIVE,TOTAL_SALES_AMT,
     CAST((((F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT)/.80)*.20) AS DECIMAL(16,2)) AS        
     SC_DISC,(0) AS PWD_DISC,F.REGULAR_DISCOUNTS_AMOUNT,(0) as NON_PROMO_DISC,      
     (F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT+F.REGULAR_DISCOUNTS_AMOUNT) AS        
     TOTAL_DISC,F.NEW_GRAND_TOTAL_VOIDS,F.DAILY_RETURNS,(0) AS ADJ, 
     (TOTAL_SALES_AMT-(F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT+F.REGULAR_DISCOUNTS_AMOUNT)-0) AS GROSS_SALES ,      
     (F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT) AS  SC_SALES,(0) AS PWD_SALES,(F.SC5_DISC_AMOUNT       
     +F.SC20_DISC_AMOUNT+0) AS NON_VAT_SALES,F.ZERO_RATED_SALES_AMOUNT       
     ,CAST((F.DAILY_GROSS_SALES-F.SC5_DISC_AMOUNT-F.SC20_DISC_AMOUNT-       
     F.ZERO_RATED_SALES_AMOUNT/1.12)AS DECIMAL(12,1)) AS VATABLE_SALES,CAST(( ((F.DAILY_GROSS_SALES-       
      F.SC5_DISC_AMOUNT-F.SC20_DISC_AMOUNT-F.ZERO_RATED_SALES_AMOUNT)*.12)/1.12) AS DECIMAL(15,2)) AS        
      VAT,CAST((F.DAILY_GROSS_SALES-(F.DAILY_GROSS_SALES-F.SC5_DISC_AMOUNT-       
      F.SC20_DISC_AMOUNT-F.ZERO_RATED_SALES_AMOUNT*.12/1.12)) AS DECIMAL(12,1))       
      AS NETSALES,  
          (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT ))   
              FROM TL_TENDER  
                 WHERE TL_TENDER.TID = F.tid AND TL_TENDER.NTND =1 and TL_TENDER.DT_DATE='2016-06-30' ) CASH,  
          (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))   
              FROM TL_TENDER  
                 WHERE TL_TENDER.TID = F.TID AND TL_TENDER.NTND =2  AND TL_TENDER.DT_DATE='2016-06-30') CHARGE,  
          (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))  
               FROM TL_TENDER  
                 WHERE TL_TENDER.TID = F.TID  AND TL_TENDER.NTND >=3 AND TL_TENDER.DT_DATE='2016-06-30') OTHER_TENDER  
,F.FROM_FISCAL_XACT,F.TO_FISCAL_XACT ,F.POS_ITEMS_COUNT,F.PREV_VOID_COUNT,(0) AS NOSALE_TRANSACTION,
F.REPORT_TYPE FROM TL_FISCALREPORT   F          
WHERE F.REPORT_TYPE=1 AND F.STOREID=1 AND F.DT_DATE ='2016-06-30' 
ORDER BY TID

您只需將其添加為選擇列表中的另一列：

 (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT ))   
              FROM TL_TENDER  
                 WHERE TL_TENDER.TID = F.tid AND TL_TENDER.NTND =1 and TL_TENDER.DT_DATE='2016-06-30' ) +  
          (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))   
              FROM TL_TENDER  
                 WHERE TL_TENDER.TID = F.TID AND TL_TENDER.NTND =2  AND TL_TENDER.DT_DATE='2016-06-30') +   
          (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))  
               FROM TL_TENDER  
                 WHERE TL_TENDER.TID = F.TID  AND TL_TENDER.NTND >=3 AND TL_TENDER.DT_DATE='2016-06-30') AS YOURTOTAL

給予：

SELECT DISTINCT F.DT_TIME,F.DT_DATE,'DEPT STORE' AS CATEGORY,
     F.STOREID,F.TID,(F.CURRENT_POSITIVE) AS NEW_GT,
     PREV_POSITIVE,TOTAL_SALES_AMT,
     CAST((((F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT)/.80)*.20) AS DECIMAL(16,2)) AS        
     SC_DISC,(0) AS PWD_DISC,F.REGULAR_DISCOUNTS_AMOUNT,(0) as NON_PROMO_DISC,      
     (F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT+F.REGULAR_DISCOUNTS_AMOUNT) AS        
     TOTAL_DISC,F.NEW_GRAND_TOTAL_VOIDS,F.DAILY_RETURNS,(0) AS ADJ, 
     (TOTAL_SALES_AMT-(F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT+F.REGULAR_DISCOUNTS_AMOUNT)-0) AS GROSS_SALES ,      
     (F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT) AS  SC_SALES,(0) AS PWD_SALES,(F.SC5_DISC_AMOUNT       
     +F.SC20_DISC_AMOUNT+0) AS NON_VAT_SALES,F.ZERO_RATED_SALES_AMOUNT       
     ,CAST((F.DAILY_GROSS_SALES-F.SC5_DISC_AMOUNT-F.SC20_DISC_AMOUNT-       
     F.ZERO_RATED_SALES_AMOUNT/1.12)AS DECIMAL(12,1)) AS VATABLE_SALES,CAST(( ((F.DAILY_GROSS_SALES-       
      F.SC5_DISC_AMOUNT-F.SC20_DISC_AMOUNT-F.ZERO_RATED_SALES_AMOUNT)*.12)/1.12) AS DECIMAL(15,2)) AS        
      VAT,CAST((F.DAILY_GROSS_SALES-(F.DAILY_GROSS_SALES-F.SC5_DISC_AMOUNT-       
      F.SC20_DISC_AMOUNT-F.ZERO_RATED_SALES_AMOUNT*.12/1.12)) AS DECIMAL(12,1))       
      AS NETSALES,  
          (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT ))   
              FROM TL_TENDER  
                 WHERE TL_TENDER.TID = F.tid AND TL_TENDER.NTND =1 and TL_TENDER.DT_DATE='2016-06-30' ) CASH,  
          (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))   
              FROM TL_TENDER  
                 WHERE TL_TENDER.TID = F.TID AND TL_TENDER.NTND =2  AND TL_TENDER.DT_DATE='2016-06-30') CHARGE,  
          (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))  
               FROM TL_TENDER  
                 WHERE TL_TENDER.TID = F.TID  AND TL_TENDER.NTND >=3 AND TL_TENDER.DT_DATE='2016-06-30') OTHER_TENDER  
,F.FROM_FISCAL_XACT,F.TO_FISCAL_XACT ,F.POS_ITEMS_COUNT,F.PREV_VOID_COUNT,(0) AS NOSALE_TRANSACTION,
F.REPORT_TYPE,
     (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT ))   
                  FROM TL_TENDER  
                     WHERE TL_TENDER.TID = F.tid AND TL_TENDER.NTND =1 and TL_TENDER.DT_DATE='2016-06-30' ) +  
              (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))   
                  FROM TL_TENDER  
                     WHERE TL_TENDER.TID = F.TID AND TL_TENDER.NTND =2  AND TL_TENDER.DT_DATE='2016-06-30') +   
              (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))  
                   FROM TL_TENDER  
                     WHERE TL_TENDER.TID = F.TID  AND TL_TENDER.NTND >=3 AND TL_TENDER.DT_DATE='2016-06-30') AS YOURTOTAL
FROM TL_FISCALREPORT   F          
WHERE F.REPORT_TYPE=1 AND F.STOREID=1 AND F.DT_DATE ='2016-06-30' 
ORDER BY TID

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

相關問答

Performance

計算嵌套的一對多關係的總數

  • February 6, 2018
檢視問答
Db2

如果沒有B，則查詢A

  • January 30, 2018
檢視問答
Performance

從子查詢中聯合還是結合兩個外部查詢更有效？

  • August 18, 2015
檢視問答
Db2

如何從 db2 中的準備好的語句參數中減去天數？

  • March 5, 2013
檢視問答
Mysql

按單個成員選擇組並對其他值求和

  • August 31, 2022
檢視問答
Mysql

MySQL查詢：檢測另一個表中已刪除的行並顯示在結果表中

  • August 31, 2022
檢視問答