将多个查询合并为单个查询

Question

我有2个查询并且调用了一个函数2次我只需要根据msg_sys_no count和msg_trans_type调用函数一次。 请找到下面提到的查询，并为我提供合并为单一的解决方案。

SELECT COUNT(DISTINCT b1.msg_sys_no) INTO A 
    FROM tra_message b1 
    WHERE TO_CHAR(b1.msg_when_created,'YYYY-MM-DD') = in_start_date 
    AND b1.msg_service_provider     = in_svc_provider 
    AND b1.msg_trans_type = 'TRADE1' 
    AND get_transaction_status_func(b1.msg_sys_no, b1.msg_trans_type) = 'S'; 

SELECT COUNT(DISTINCT b1.msg_sys_no) INTO B 
    FROM tra_message b1 
    WHERE TO_CHAR(b1.msg_when_created,'YYYY-MM-DD') = in_start_date 
    AND b1.msg_service_provider     = in_svc_provider 
    AND b1.msg_trans_type = 'TRADE2' 
    AND get_transaction_status_func(b1.msg_sys_no, b1.msg_trans_type) = 'S'; 

Answer 1

大约是这样的内容：

WITH tra_data 
    AS (SELECT * 
      FROM tra_message 
      WHERE TO_CHAR (msg_when_created, 'YYYY-MM-DD') = in_start_date 
       AND msg_service_provider = in_svc_provider 
       AND get_transaction_status_func (msg_sys_no, msg_trans_type) = 
         'S') 
SELECT COUNT (*) 
    FROM tra_data 
WHERE msg_trans_type = 'TRADE1' 
UNION 
SELECT COUNT (*) 
    FROM tra_data 
WHERE msg_trans_type = 'TRADE2' 

Answer 2

的问题是，您和IN b1.msg_trans_type（ 'TRADE1'， 'TRADE2'）的条件。

尝试是这样的：

select COUNT(DISTINCT a) TRADE1, 
     COUNT(DISTINCT b) TRADE2 
into A,B 
from (
select case when b1.msg_trans_type = 'TRADE1' 
      then b1.msg_sys_no 
      else null end as a, 
     case when b1.msg_trans_type = 'TRADE2' 
      then b1.msg_sys_no 
      else null end as b 
FROM tra_message b1 
WHERE TO_CHAR(b1.msg_when_created,'YYYY-MM-DD') = in_start_date 
AND b1.msg_service_provider = in_svc_provider 
AND b1.msg_trans_type IN ('TRADE1','TRADE2') 
AND get_transaction_status_func(b1.msg_sys_no, b1.msg_trans_type) = 'S' 
);