SQLITE Vlookup在2个不同的表中

Question

我有 2 个表，其中 1 个银行数据是付款确认，我需要执行查找。

我尝试了一些左连接示例，但无法获得所需的输出。我正在使用 Sqlite3

请提出建议

表 - Payment_Details

Payment_Date    TranNo      RefNo       ChequeNo
11-Nov-19       tran001     ref001      chq001
11-Nov-19       tran001     ref002      chq005
12-Nov-19       tran003     ref003      chq007
13-Nov-19       tran017     ref001      chq005

表 - Payment_Confirmations

CustomerID      Payment_No      Match_Status        Payment_date
cust_1      ref003              
cust_2      tran001             
cust_3      ref002              
cust_4      tran019             
cust_5      tran001             
cust_6      chq005              
cust_7      tran019             

输出表 - Payment_Confirmations

CustomerID  Payment_No Match_Status   Payment_date
cust_1      ref003      TRUE          12-Nov-19
cust_2      tran001     TRUE          11-Nov-19
cust_3      ref002      TRUE          11-Nov-19
cust_4      tran019     FALSE         FALSE
cust_5      tran001     TRUE          11-Nov-19
cust_6      chq005      TRUE          11-Nov-19
cust_7      tran019     FALSE         FALSE

Answer 1

尝试以下操作:

表架构:

CREATE TABLE Payment_Details(
  Payment_Date DATETIME,
  TranNo VARCHAR(20),
  RefNo VARCHAR(20),
  ChequeNo VARCHAR(20));

 INSERT INTO Payment_Details VALUES
('11-Nov-19','tran001','ref001','chq001'),
('11-Nov-19','tran001','ref002','chq005'),
('12-Nov-19','tran003','ref003','chq007'),
('13-Nov-19','tran017','ref001','chq005');

 CREATE TABLE Payment_Confirmations(
  CustomerId VARCHAR(20),
  Payment_No VARCHAR(20),
  Match_Status VARCHAR(20),
  Payment_date DATETIME);

INSERT INTO Payment_Confirmations(CustomerId,Payment_No) VALUES
('cust_1','ref003'),
('cust_2','tran001'),
('cust_3','ref002'),
('cust_4','tran019'),
('cust_5','tran001'),
('cust_6','chq005'),
('cust_7','tran019');

SQL 查询:

SELECT DISTINCT A.CustomerID,
    A.Payment_No,
    CASE WHEN B.ChequeNo IS NULL THEN 'FALSE' ELSE 'TRUE' END Match_Status,
    IFNULL(b.Payment_date,'FALSE') Payment_date
FROM Payment_Confirmations A
LEFT JOIN Payment_Details B ON A.Payment_No=B.TranNo OR A.Payment_No=B.RefNo OR A.Payment_No=B.ChequeNo;

检查 SQL Fiddle 中的结果