前端之家

  1. 首页
  2. 问答

2个不同表中的SQLITE Vlookup

• 问答

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

我尝试了几个left join示例，但无法获得所需的输出。 我正在使用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
jyc2410 回答：2个不同表中的SQLITE Vlookup

尝试以下操作：

表架构： 

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','ref002','chq005'),('12-Nov-19','tran003','ref003','chq007'),('13-Nov-19','tran017','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',('cust_6',('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

中检查结果
sqlsqlitevlookup
本文链接：https://www.f2er.com/3125286.html

大家都在问