时间戳

首页 > 解决方案 > 如何将这些查询组合成一个查询？还是一个 pl/sql 存储过程？

sql - 如何将这些查询组合成一个查询？还是一个 pl/sql 存储过程？

问题描述

我想用 jaspersoft studio 和 oracle pl/sql 数据源构建一个报告，所以我想结合这些查询来编写程序。我正在尝试制作与此类似的报告。 在此处输入图像描述

第一个查询：在图像中显示 X 1。

SELECT SUM(BD.QUANTITY)   
    FROM BRANCHS_MOVE_DTLS BD,BRANCHS_MOVES BM  
   WHERE BD.BRNCHMOVE_ID = BM.ID  
     AND BD.BRNCHMOVE_CURR_ID = BM.CURR_ID  
     AND BD.CLNT_TYPE_ID IN ( 3,4  ) 
     AND BD.SUPPLIER_ID = 1  
     AND BD.PTITMMOVTP_ID = 2   
     AND BM.MONTH = &b3  
     AND BM.MOVE_TYPE = 2  
     AND BD.PITMMVDLTP_ID = 2

2- 在图像中显示 X 2。

SELECT SUM(BD.QUANTITY)   
    FROM BRANCHS_MOVE_DTLS BD,BRANCHS_MOVES BM  
   WHERE BD.BRNCHMOVE_ID = BM.ID  
     AND BD.BRNCHMOVE_CURR_ID = BM.CURR_ID  
     AND BD.CLNT_TYPE_ID IN ( 3,4  ) 
     AND BD.SUPPLIER_ID = 2 
     AND BD.PTITMMOVTP_ID = 2   
     AND BM.MONTH = &b3  
     AND BM.MOVE_TYPE = 2  
     AND BD.PITMMVDLTP_ID = 2

3- 显示 Y 1

SELECT SUM(BD.QUANTITY)   
  FROM BRANCHS_MOVE_DTLS BD,BRANCHS_MOVES BM  
 WHERE BD.BRNCHMOVE_ID = BM.ID  
   AND BD.BRNCHMOVE_CURR_ID = BM.CURR_ID  
   AND BD.CLNT_TYPE_ID = 2  
   AND BD.SUPPLIER_ID = 1  
   AND BD.PTITMMOVTP_ID = 2  
   AND BM.MONTH = &b3   
   AND BM.MOVE_TYPE = 2  
   AND BD.PITMMVDLTP_ID = 2

4- 显示 Y 2

 SELECT SUM(BD.QUANTITY)   
  FROM BRANCHS_MOVE_DTLS BD,BRANCHS_MOVES BM  
 WHERE BD.BRNCHMOVE_ID = BM.ID  
   AND BD.BRNCHMOVE_CURR_ID = BM.CURR_ID  
   AND BD.CLNT_TYPE_ID = 2  
   AND BD.SUPPLIER_ID = 2   
   AND BD.PTITMMOVTP_ID = 2  
   AND BM.MONTH = &b3  
   AND BM.MOVE_TYPE = 2  
   AND BD.PITMMVDLTP_ID = 2

5-显示 Z 1

SELECT SUM(BD.QUANTITY)   
  FROM BRANCHS_MOVE_DTLS BD,BRANCHS_MOVES BM  
 WHERE BD.BRNCHMOVE_ID = BM.ID  
   AND BD.BRNCHMOVE_CURR_ID = BM.CURR_ID  
   AND BD.CLNT_TYPE_ID = 11  
   AND BD.SUPPLIER_ID = 1  
   AND BD.PTITMMOVTP_ID = 2    
   AND BM.MONTH = &b3  
   AND BM.MOVE_TYPE = 2  
   AND BD.PITMMVDLTP_ID = 2

6- 显示 Z 2

SELECT SUM(BD.QUANTITY)   
  FROM BRANCHS_MOVE_DTLS BD,BRANCHS_MOVES BM  
 WHERE BD.BRNCHMOVE_ID = BM.ID  
   AND BD.BRNCHMOVE_CURR_ID = BM.CURR_ID  
   AND BD.CLNT_TYPE_ID = 11  
   AND BD.SUPPLIER_ID = 2  
   AND BD.PTITMMOVTP_ID = 2   
   AND BM.MONTH = &b3   
   AND BM.MOVE_TYPE = 2  
   AND BD.PITMMVDLTP_ID = 2

7-这个查询得到几个月

SELECT SBM.ID MONTH_ID ,SBM.NAME MONTH_NAME FROM SB_MONTH SBM ORDER BY SBM.ID

所以这些查询返回表中 &b3 = 1 的第一行，&b3 是月份的参数，我应该从查询号 7 中填充它。所以当 &b3 为 1 时应该返回第一行，&b3 = 4 应该返回第 4 行。

知道如何结合这些要求来实现吗？或任何建议

我的问题是我不明白如何结合这些查询来创建存储过程并构建报告。

标签： sqloracleplsql

解决方案

您可以单独计算所有需要的聚合，然后将它们加入到您的表中，以月份为单位。如果需要，Oracle 优化器可以将连接谓词推送到子查询中：

SELECT 
   SBM.ID MONTH_ID 
  ,SBM.NAME MONTH_NAME
  ,agg.x1
  ,agg.x2
  ,agg.x3
  ,agg.x4
  ,agg.x5
  ,agg.x6
FROM SB_MONTH SBM 
     join (
       SELECT BM.MONTH
             ,SUM(case when BD.CLNT_TYPE_ID IN ( 3,4  ) AND BD.SUPPLIER_ID = 1 then BD.QUANTITY end) x1
             ,SUM(case when BD.CLNT_TYPE_ID IN ( 3,4  ) AND BD.SUPPLIER_ID = 2 then BD.QUANTITY end) x2
             ,SUM(case when BD.CLNT_TYPE_ID = 2         AND BD.SUPPLIER_ID = 1 then BD.QUANTITY end) x3
             ,SUM(case when BD.CLNT_TYPE_ID = 2         AND BD.SUPPLIER_ID = 2 then BD.QUANTITY end) x4
             ,SUM(case when BD.CLNT_TYPE_ID = 11        AND BD.SUPPLIER_ID = 1 then BD.QUANTITY end) x5
             ,SUM(case when BD.CLNT_TYPE_ID = 11        AND BD.SUPPLIER_ID = 2 then BD.QUANTITY end) x5
        FROM BRANCHS_MOVE_DTLS BD
            ,BRANCHS_MOVES BM  
       WHERE BD.BRNCHMOVE_ID = BM.ID  
         AND BD.BRNCHMOVE_CURR_ID = BM.CURR_ID  
         AND BD.CLNT_TYPE_ID IN ( 3,4,2,11  )
         AND BD.SUPPLIER_ID IN (1,2)
         AND BD.PTITMMOVTP_ID = 2   
         AND BM.MOVE_TYPE = 2  
         AND BD.PITMMVDLTP_ID = 2
       group by BM.MONTH
     ) agg
     on agg.MONTH = SBM.ID
ORDER BY SBM.ID;

推荐阅读