数据匹配时使用SQL Join [英] SQL Join with when data matches
本文介绍了数据匹配时使用SQL Join的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有两个表table1和table2
I have two tables table1 and table2
CREATE TABLE TABLE1 (
EMPLID VARCHAR2(11) NOT NULL,
COMPANY VARCHAR2(3) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
BALANCE_ID VARCHAR2(2) NOT NULL,
BALANCE_YEAR SMALLINT NOT NULL,
BALANCE_QTR SMALLINT NOT NULL,
BALANCE_PERIOD SMALLINT NOT NULL,
SPCL_BALANCE VARCHAR2(1) NOT NULL,
ERNCD VARCHAR2(3) NOT NULL,
HRS_QTD DECIMAL(13, 2) NOT NULL,
GRS_QTD DECIMAL(13, 2) NOT NULL,
HRS_YTD DECIMAL(13, 2) NOT NULL,
GRS_YTD DECIMAL(13, 2) NOT NULL,
HRS_MTD DECIMAL(13, 2) NOT NULL,
GRS_MTD DECIMAL(13, 2) NOT NULL);
CREATE TABLE TABLE2(EMPLID VARCHAR2(11) NOT NULL,
COMPANY VARCHAR2(3) NOT NULL,
BALANCE_ID VARCHAR2(2) NOT NULL,
BALANCE_YEAR SMALLINT NOT NULL,
BALANCE_QTR SMALLINT NOT NULL,
BALANCE_PERIOD SMALLINT NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
SPCL_BALANCE VARCHAR2(1) NOT NULL,
ERNCD VARCHAR2(3) NOT NULL,
HRS_YTD DECIMAL(13, 2) NOT NULL,
HRS_QTD DECIMAL(13, 2) NOT NULL,
HRS_MTD DECIMAL(13, 2) NOT NULL,
GRS_YTD DECIMAL(13, 2) NOT NULL,
GRS_QTD DECIMAL(13, 2) NOT NULL,
GRS_MTD DECIMAL(13, 2) NOT NULL);
我想编写一个查询以从table1和table2中获取数据，并返回table1中的所有行以及table2中的匹配"值 匹配值意味着，当EMPLID，ERNCD匹配时，它应该显示行. EMPLID&之间的所有字段SPCL_BALANCE是键.
I would like to write a query to fetch data from table1 and table2 returning all the rows from table1 and "matching" value from table2 The matching values imply, when EMPLID,ERNCD matches it should display the rows. All the fields between EMPLID & SPCL_BALANCE are keys.
表1的样本数据:
INSERT INTO table1 VALUES('XX','C','0','CY','2015','2','4','Y','XYZ','0','0','0','0','100001','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2015','2','5','Y','XYZ','0','0','0','0','100002','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2015','2','6','Y','XYZ','0','0','0','0','100003','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2015','3','7','Y','XYZ','0','0','0','0','100004','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2015','3','9','Y','XYZ','0','0','0','0','100005','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2015','4','10','Y','XYZ','0','0','0','0','100006','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2015','4','12','Y','XYZ','0','0','0','0','100001','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2016','1','1','Y','XYZ','0','0','0','0','100002','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2016','1','2','Y','XYZ','0','0','0','0','100003','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2016','1','3','Y','XYZ','0','0','0','0','100004','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2016','2','4','Y','XYZ','0','0','0','0','100005','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2016','2','6','Y','XYZ','0','0','0','0','100006','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2016','2','6','Y','DCP','0','100001','0','100001','0','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2016','2','6','Y','SAV','0','100002','0','100002','0','0');
INSERT INTO table1 VALUES('XX','C','0','CY','2016','2','6','Y','SUP','0','100003','0','100003','0','96949.98');
INSERT INTO table1 VALUES('XX','C','0','FY','2016','4','11','Y','PER','0','100004','0','100004','0','0');
INSERT INTO table1 VALUES('XX','C','0','FY','2016','4','11','Y','RET','0','100005','0','100005','0','0');
INSERT INTO table1 VALUES('XX','C','0','FY','2016','4','11','Y','SUM','0','100006','0','100006','0','0');
表2的样本数据
INSERT INTO table2 VALUES('XX','C','CY','2015','2','4','0','Y','XYZ','0','0','176','0','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2015','2','5','0','Y','XYZ','0','0','176','0','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2015','2','6','0','Y','XYZ','0','0','168','0','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2015','3','7','0','Y','XYZ','0','0','360','0','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2015','3','9','0','Y','XYZ','0','0','168','0','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2015','4','10','0','Y','XYZ','0','0','352','0','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2015','4','12','0','Y','XYZ','0','0','168','0','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2016','1','1','0','Y','XYZ','0','0','184','0','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2016','1','2','0','Y','XYZ','0','0','168','0','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2016','1','3','0','Y','XYZ','0','0','168','0','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2016','2','4','0','Y','XYZ','0','0','352','0','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2016','2','5','0','Y','DCP','0','0','0','100001','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2016','2','5','0','Y','SAV','0','0','0','100002','0','0')
INSERT INTO table2 VALUES('XX','C','CY','2016','2','5','0','Y','SUP','0','0','0','100003','0','0')
INSERT INTO table2 VALUES('XX','C','FY','2016','4','10','0','Y','PER','0','0','0','100004','0','0')
INSERT INTO table2 VALUES('XX','C','FY','2016','4','10','0','Y','RET','0','0','0','100005','0','0')
我想编写一个查询，无论EMPLID和ERNCD是否匹配，都显示来自table1的所有18行，而与其他键是否匹配无关.
I would like to write a query which displays all 18 rows from table1 whenever the EMPLID and ERNCD matches, irrespective of the other keys matches or not.
输出样本
t1.emplid t2.emplid t1.company t2.compnay t1.empl_rcd t2.empl_rcd t1.balance_id t2.balance_id t1.balance_year t2.balance_year t1.balance_qtr t2.balance_qtr t1.balance_period t2.balance_period t1.erncd t2.erncd
10011024 10011024 UCS UCS 0 0 CY CY 2015 2015 2 2 4 4 ACA ACA
10011024 10011024 UCS UCS 0 0 CY CY 2015 2015 2 2 5 5 ACA ACA
10011024 10011024 UCS UCS 0 0 CY CY 2015 2015 2 2 6 6 ACA ACA
10011024 10011024 UCS UCS 0 0 CY CY 2015 2015 3 3 7 7 ACA ACA
10011024 10011024 UCS UCS 0 0 CY CY 2015 2015 3 3 9 9 ACA ACA
10011024 10011024 UCS UCS 0 0 CY CY 2015 2015 4 4 10 10 ACA ACA
10011024 10011024 UCS UCS 0 0 CY CY 2015 2015 4 4 12 12 ACA ACA
10011024 10011024 UCS UCS 0 0 CY CY 2016 2016 1 1 1 1 ACA ACA
10011024 10011024 UCS UCS 0 0 CY CY 2016 2016 1 1 2 2 ACA ACA
10011024 10011024 UCS UCS 0 0 CY CY 2016 2016 1 1 3 3 ACA ACA
10011024 10011024 UCS UCS 0 0 CY CY 2016 2016 2 2 4 4 ACA ACA
10011024 NULL UCS NULL 0 NULL CY NULL 2016 NULL 2 NULL 6 NULL ACA NULL
10011024 10011024 UCS UCS 0 0 CY CY 2016 2016 2 2 6 5 DCP DCP
10011024 10011024 UCS UCS 0 0 CY CY 2016 2016 2 2 6 5 SAV SAV
10011024 10011024 UCS UCS 0 0 CY CY 2016 2016 2 2 6 5 SUP SUP
10011024 10011024 UCS UCS 0 0 FY FY 2016 2016 4 4 11 10 PER PER
10011024 10011024 UCS UCS 0 0 FY FY 2016 2016 4 4 11 10 RET RET
10011024 NULL UCS NULL 0 NULL CY NULL 2016 NULL 4 NULL 11 NULL SUM NULL
我希望这会有所帮助.我目前正在使用以下sql，但未获取所有行:
I hope this helps. I am currently using the sql below but not getting all the rows:
FROM
(
select EBCS1.EMPLID ,EBCS1.COMPANY ,EBCS1.BALANCE_ID ,EBCS1.BALANCE_YEAR ,EBCS1.BALANCE_QTR ,
EBCS1.BALANCE_PERIOD ,EBCS1.EMPL_RCD ,EBCS1.SPCL_BALANCE ,EBCS1.ERNCD ,EBCS1.HRS_YTD ,EBCS1.HRS_QTD ,EBCS1.HRS_MTD ,
EBCS1.GRS_YTD ,EBCS1.GRS_QTD ,EBCS1.GRS_MTD
row_number() over (partition by EBCS1.EMPLID order by case when EBCS1.EMPLID is not null then 0 else 1 end asc, EBCS1.ERNCD) rn
from table1 EBCS1 left join
(select distinct EMPLID,EMPL_RCD, COMPANY, BALANCE_ID,BALANCE_YEAR,BALANCE_QTR,BALANCE_PERIOD,ERNCD,SPCL_BALANCE from table2) EBPS1 on
EBCS1.EMPLID=EBPS1.EMPLID AND EBCS1.BALANCE_ID=EBPS1.BALANCE_ID and EBCS1.COMPANY=EBPS1.COMPANY AND EBCS1.ERNCD=EBPS1.ERNCD and EBCS1.BALANCE_PERIOD = EBPS1.BALANCE_PERIOD
AND EBCS1.BALANCE_QTR = EBPS1.BALANCE_QTR AND EBCS1.EMPL_RCD = EBPS1.EMPL_RCD AND EBCS1.BALANCE_YEAR = EBPS1.BALANCE_YEAR
AND EBCS1.SPCL_BALANCE =EBPS1.SPCL_BALANCE ) EBCS
LEFT OUTER JOIN
(
select EBPS1.EMPLID ,EBPS1.COMPANY ,EBPS1.BALANCE_ID ,EBPS1.BALANCE_YEAR ,EBPS1.BALANCE_QTR ,
EBPS1.BALANCE_PERIOD ,EBPS1.EMPL_RCD ,EBPS1.SPCL_BALANCE ,EBPS1.ERNCD ,EBPS1.HRS_YTD ,EBPS1.HRS_QTD ,EBPS1.HRS_MTD ,
EBPS1.GRS_YTD ,EBPS1.GRS_QTD ,EBPS1.GRS_MTD,
row_number() over (partition by EBPS1.EMPLID order by case when EBPS1.EMPLID is not null then 0 else 1 end asc, EBPS1.ERNCD) rn
from table2 EBPS1 left join (select distinct EMPLID,EMPL_RCD,COMPANY,BALANCE_ID,BALANCE_YEAR,BALANCE_PERIOD,ERNCD,SPCL_BALANCE,BALANCE_QTR
from table1) EBCS1 ON
EBCS1.EMPLID=EBPS1.EMPLID AND EBCS1.BALANCE_ID=EBPS1.BALANCE_ID and EBCS1.COMPANY=EBPS1.COMPANY AND EBCS1.ERNCD=EBPS1.ERNCD and EBCS1.BALANCE_PERIOD = EBPS1.BALANCE_PERIOD
AND EBCS1.BALANCE_QTR = EBPS1.BALANCE_QTR AND EBCS1.EMPL_RCD = EBPS1.EMPL_RCD AND EBCS1.BALANCE_YEAR = EBPS1.BALANCE_YEAR AND EBCS1.SPCL_BALANCE =EBPS1.SPCL_BALANCE ) EBPS
ON ( EBPS.EMPLID=EBCS.EMPLID AND EBPS.COMPANY=EBCS.COMPANY AND EBCS.rn=EBPS.rn )
使用以下查询输出:
XX1 CY CY 2 2 1 1 ACA ACA
XX1 CY CY 3 3 1 1 ACA ACA
XX1 CY CY 4 4 2 2 ACA ACA
XX1 CY 6 0 2 0 ACA
XX1 CY 6 0 2 0 DCP
XX1 CY 6 0 2 0 SAV
XX1 CY 6 0 2 0 SUP
XX1 FY 11 0 4 0 SAF
预期输出:
XX1 CY CY 2 2 1 1 ACA ACA
XX1 CY CY 3 3 1 1 ACA ACA
XX1 CY CY 4 4 2 2 ACA ACA
XX1 CY NULL 6 0 2 0 ACA NULL
XX1 CY CY 6 2 2 5 DCP DCP
XX1 CY CY 6 2 2 5 SAV SAV
XX1 CY CY 6 2 2 5 SUP SUP
XX1 FY FY 11 4 10 10 SAF SAF
推荐答案
我想到的唯一想法是:
select t1.emplid,t1.balance_id,t2.balance_id,t1.balance_period,t2.balance_period,t1.balance_qtr,t2.balance_qtr, t1.erncd,t2.erncd
from table1 t1 join table2 t2
on t1.emplid=t2.emplid
and t1.company=t2.company
and t1.empl_rcd=t2.empl_rcd
and t1.balance_id=t2.balance_id
and t1.balance_year=t2.balance_year
and t1.balance_qtr=t2.balance_qtr
and t1.balance_period=t2.balance_period
and t1.spcl_balance=t2.spcl_balance
and t1.erncd=t2.erncd
union all
select t1.emplid,
t1.balance_id, null balance_id,
t1.balance_period, null balance_period,
t1.balance_qtr, null balance_qtr,
t1.erncd, null erncd
from table1 t1
left join (select * from table2 t3
where not exists
( select 1 from table1 t4
where t3.emplid=t4.emplid
and t3.company=t4.company
and t3.empl_rcd=t4.empl_rcd
and t3.balance_id=t4.balance_id
and t3.balance_year=t4.balance_year
and t3.balance_qtr=t4.balance_qtr
and t3.balance_period=t4.balance_period
and t3.spcl_balance=t4.spcl_balance
)
) t3
on t1.emplid=t3.emplid
and t1.erncd=t3.erncd
where not exists
( select 1 from table2 t2
where t1.emplid=t2.emplid
and t1.company=t2.company
and t1.empl_rcd=t2.empl_rcd
and t1.balance_id=t2.balance_id
and t1.balance_year=t2.balance_year
and t1.balance_qtr=t2.balance_qtr
and t1.balance_period=t2.balance_period
and t1.spcl_balance=t2.spcl_balance
);
在查询的第一部分中，我返回所有完全匹配的行.在第二个中，我返回具有相同emplid和erncd的table1与table2联接的所有行(但仅在不匹配的记录上).
In the first part of the query I'm returning all the completely matching rows. In the second one I'm returning all the rows of table1 joined with table2 with the same emplid and erncd (but only on the not matching records).
如果这不能满足您的要求，请您在示例中添加table2行和完整的预期结果吗?
If that's not responding to your requisites, could you please add the table2 rows in your example and the full expected result?
我不清楚第二个联合是否会使用您的数据返回重复项.
It's not clear to me if with your data the second union would return duplicates or not.
这篇关于数据匹配时使用SQL Join的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
