SQL连接具有唯一行 [英] SQL Join with unique rows

问题描述

我有两个桌子 表1

 - EMPLID XX_EMPLID GTN   DEDCD    EFFDT
     1     A1        102   XXYY     02-OCT-16
     1     A1        103   XXYZ     02-OCT-16

表2

  - EMPLID  DEDCD    EFFDT
     1       XXYA     02-OCT-16
     1       XXYZ     02-OCT-16

当我加入时，select应该只返回两行，输出应该是

When I join the select should return only two rows and the output should be

 - EMPLID   2.DEDCD    EFFDT        1.DEDCD
     1       XXYA     02-OCT-16      XXYY
     1       XXYZ     02-OCT-16      XXYZ

EMPLID，EFFDT是键.我不需要加入DEDCD，因为我需要查看两者之间的区别(如果有).请提出建议.

EMPLID,EFFDT being the key. I do not have want to join DEDCD as I need to the see the difference if any. Please suggest.

简单的连接总是会给我更多的行.我希望将行限制为Table1中的行数.我尝试了右外部连接"和左外部连接"

Simple join will give me more rows always. I am looking to limit the row to the number of rows in Table1. I tried "Right outer join" and "left outer join" but does not work

在获得一些帮助之后，我能够接近结果.但是，下面指出了我面临的一个问题

After some help, I am able to get close to the results. However, one issue that I am facing is pointed out below

10008536    XXYZ    06-MAR-02   (XXYY)  06-MAR-02
10008536    (XXYY)  06-MAR-02   XXZZ    06-MAR-02

编辑3

尽管我设法解决了先前的问题，但我面临着另一个类似的问题.

Although I managed to solve my previous problem, I am facing yet another similar problem.

两个表的键结构为 EMPLID，EMPL_RCD，BALANCE_ID，BALANCE_YEAR，BALANCE_QTR，BALANCE_PERIOD，ERNCD

two tables with key structure as EMPLID,EMPL_RCD, BALANCE_ID, BALANCE_YEAR, BALANCE_QTR,BALANCE_PERIOD,ERNCD

我编写了一个查询，使用下面的SQL从两个表中获取值:

I wrote a query to fetch the value from both tables using the SQL Below:

SELECT EBPS.EMPLID ,EBPS.BALANCE_ID ,EBPS.BALANCE_YEAR ,EBPS.BALANCE_QTR ,EBPS.BALANCE_PERIOD ,
EBPS.ERNCD ,EBCS.EMPLID ,EBCS.BALANCE_ID ,EBCS.BALANCE_YEAR ,EBCS.BALANCE_QTR ,
EBCS.BALANCE_PERIOD ,EBCS.ERNCD   
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 ,EBCS1.XX_EMPLOYEE_ID,
         row_number() over (partition by EBCS1.EMPLID,EBCS1.EMPL_RCD order by case when EBPS1.EMPLID is not null then 0 else 1 end asc,
         EBCS1.BALANCE_YEAR,EBCS1.BALANCE_QTR,EBCS1.BALANCE_PERIOD,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,EBPS1.EMPL_RCD order by case when EBCS1.EMPLID is not null then 0 else 1 end asc,
         EBCS1.BALANCE_YEAR,EBCS1.BALANCE_QTR,EBCS1.BALANCE_PERIOD,EBCS1.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 EBCS.EMPL_RCD=EBPS.EMPL_RCD   AND  EBCS.rn=EBPS.rn  )

但是，如果表Table2中缺少一行，则会得到错误的结果.例如，如果某个员工在表1中有16行，在表2中有15行，并且ERNCD与某些行匹配，但Period/Quarter不匹配，在这种情况下，我想显示不匹配Period/Quarter但匹配ERNCD的数据.但是目前，如果发生不匹配，行将变得无序.请让我知道是否有人可以解决此问题.如果您有任何疑问，请告诉我.

However, if a row is missing from table Table2 , I get incorrect result. For example if an employee has 16 rows in Table 1 and 15 in table 2 , and for some rows the ERNCD matches but Period/Quarter does not, in that case I would like to display data with unmatched Period/Quarter but matching ERNCD. But at present the rows get unorganized if a mismatch occurs. Please let me know if anyone can figure this issue out. Please let me know if you have any questions.

推荐答案

好的，让我们看看这个答案是否适合您的要求.

Ok, let's see if this answer suits your request.

SELECT a.EMPLID,a.DEDCD, to_char(a.EFFDT,'YYYY-MM-DD') EFFDT, b.DEDCD as DEDCD2,GTN 
   FROM 
   (
   select  EFFDT,GTN,EMPLID,DEDCD,
        row_number() over (partition by EMPLID order by DEDCD) rn
 from table1 ) A
    LEFT OUTER JOIN 
    (
   select  EFFDT,EMPLID,DEDCD,
        row_number() over (partition by EMPLID order by DEDCD) rn
 from table2       
    ) B
 ON ( A.EMPLID=B.EMPLID AND A.EFFDT=B.EFFDT AND a.rn=b.rn)

我假设在这里，您只是想让并排显示通过EMPLID和EFFDT链接的两个表的信息，并且table1和table2对于相同的键始终具有相同的记录号.

I'm assuming here you're just interested in having side by side the info of the two tables linked by EMPLID and EFFDT, and that table1 and table2 will always have the same record number for the same key.

我为您创建了一个示例，供您在 rextester.com 进行测试.

I've created an example for you to test at rextester.com.

已新版本

我还尝试管理在此处作为注释提交的新要求". 它变得越来越复杂，但是我希望这是您所需要的:

I've tried to manage also the new "requirements" that have been submitted here as comments. It's getting complicated, but I hope that's what you need:

 SELECT a.EMPLID,a.DEDCD, to_char(a.EFFDT,'YYYY-MM-DD') EFFDT, b.DEDCD as DEDCD2,GTN
    FROM 
    (
    select  t1.EFFDT,t1.GTN,t1.EMPLID,t1.DEDCD,
         row_number() over (partition by t1.EMPLID order by case when t2.EMPLID is not null then 0 else 1 end asc, t1.DEDCD) rn
  from table1 t1 left join (select distinct EMPLID,   EFFDT, DEDCD from    table2) t2 on  t1.EMPLID=t2.EMPLID AND t1.EFFDT=t2.EFFDT  and t1.DEDCD=t2.DEDCD
        ) A
     LEFT OUTER JOIN 
     (
    select  t2.EFFDT,t2.EMPLID,t2.DEDCD,
         row_number() over (partition by t2.EMPLID order by case when t1.EMPLID is not null then 0 else 1 end asc, t2.DEDCD) rn
  from table2 t2  left join (select distinct EMPLID,   EFFDT, DEDCD from    table1) t1 on  t1.EMPLID=t2.EMPLID AND t1.EFFDT=t2.EFFDT and t1.DEDCD=t2.DEDCD
     ) B
  ON ( A.EMPLID=B.EMPLID AND A.EFFDT=B.EFFDT AND a.rn=b.rn)

我为您创建了一个示例，供您在 rextester.com

I've created an example for you to test at rextester.com

这篇关于SQL连接具有唯一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！