条件连接| SQL |优化性能和减少no.的连接

Question

我有以下表格:

t1:

create table t1 (auth_date string,
auth_nbr int,
debit_date string,
debit_nbr int,
txn_dttm string,
iss_id string,
audit_num int);
insert into t1 values 
('01-05-2021',12,null,null,'01-05-2021','b',124),
(null,null,'02-05-2021',13,'02-05-2021','c',125),
('02-05-2021',14,'02-05-2021',14,'02-05-2021','d',126);

t2:

create table t2 (txn_amt int,txn_dttm string,iss_id string,audit_num int);
insert into t2 values 
(2000,'01-05-2021','b',124),
(2500,'02-05-2021','c',125),
(1000,'02-05-2021','d',126);

我想要的输出:

dw_date|dw_nbr|amt
01-05-2021|12|2000
02-05-2021|13|2500
02-05-2021|14|1000

我有下面的查询。但是由于加入是一个繁重的操作，我想把它缩小到只有一个加入。但是我在这两种情况下的连接是不同的条件即在第一种情况下连接是在3个cols上而在另一种情况下连接是在2个cols上。我想找到一种方法来实现我想要的输出。有条件的连接。或者其他任何东西。从t2表中，我只需要相关的txn_amt。

select dw_date,dw_nbr,amt 
from (select t1.auth_date,t1.auth_nbr,t2.amt from t1 join t2 on t1.txn_dttm=t2.txn_dttm and t1.iss_id=t2.iss_id and t1.audit_num=t2.audit_num 
where auth_date is not null and auth_nbr is not null and debit_date is null and debit_nbr is null)a 
union all 
select dw_date,dw_nbr,amt 
from (select t1.debit_date,t1.debit_nbr,t2.amt from t1 join t2 on t1.txn_dttm=t2.txn_dttm and t1.audit_num=t2.audit_num 
where debit_date is not null and debit_nbr is not null)a;

Answer 1

请检查这个。由于最新版本的MYSQL使用了CTE，因此可以避免多次使用大型表。

-- MySQL(v5.8)
WITH a_cte AS (
SELECT t1.auth_date, t1.auth_nbr, t2.txn_amt
, t1.debit_date, t1.debit_nbr
, CASE WHEN t1.iss_id = t2.iss_id
THEN 1
ELSE 0
END iss_id
FROM t1
INNER JOIN t2
ON t1.txn_dttm = t2.txn_dttm 
AND t1.audit_num = t2.audit_num 
), b_cte AS (
SELECT auth_date AS dw_date
, auth_nbr AS dw_nbr
, txn_amt AS amt
FROM a_cte
WHERE iss_id = 1
AND auth_date is not null 
AND auth_nbr is not null 
AND debit_date is null 
AND debit_nbr is null

UNION ALL

SELECT debit_date AS dw_date
, debit_nbr AS dw_nbr
, txn_amt AS amt
FROM a_cte
WHERE debit_date is not null 
AND debit_nbr is not null 
)
SELECT *
FROM b_cte;

请从url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1a482589e2cdc1f6b288c93f88150fa5查看