mysql - 如何使用条件对 mysql 数据库进行不同的连接

Question

我有两个表要匹配。当记录在同一个组名中时，我想让 sourcelist 中的 referredbyname 将名称列匹配到 targetjoin

我想通过一对一匹配加入这两个表。 targetjoin 中的联系人具有多个同名的记录类型。我想根据记录类型名称的层次结构设置进行某种类型的不同连接

例如:如果一条记录与同名的两个记录类型匹配，则 RecordType: TypeA 将仅匹配，依此类推。

源列表:

           ID  GroupName  Name          ReferredbyName        
           1   A          John Smith    Sally Bosh
           2   A          Craig Miller  Sally Smith
           3   A          Fulton Fork   Spoon Knife
           4   B          Joe Sample    George Test

目标加入:

   ID   GroupName     Name         RecordType
   101   A            Sally Bosh   TypeA
   102   A            Sally Bosh   TypeB
   103   A            Sally Smith  TypeC
   104   A            Sally Smith  TypeD
   105   B            George Test  TypeF

我的结果:

    | id | groupname |         name | referredbyname |  id | groupname |        name | recordtype |
    |----|-----------|--------------|----------------|-----|-----------|-------------|------------|
    |  2 |         A | Craig Miller |    Sally Smith | 103 |         A | Sally Smith |      TypeC |
    |  1 |         A |   John Smith |     Sally Bosh | 102 |         A |  Sally Bosh |      TypeB |
    |  1 |         A |   John Smith |     Sally Bosh | 101 |         A |  Sally Bosh |      TypeA |
    |  2 |         A | Craig Miller |    Sally Smith | 104 |         A | Sally Smith |      TypeD |
    |  4 |         B |   Joe Sample |    George Test | 105 |         B | George Test |      TypeF |

这个结果给了我所有可能的一对多连接与重复 ID 的匹配

我想要这样的结果:

    | id | groupname |         name | referredbyname |  id | groupname |        name | recordtype |
    |----|-----------|--------------|----------------|-----|-----------|-------------|------------|
    |  2 |         A | Craig Miller |    Sally Smith | 103 |         A | Sally Smith |      TypeC |
    |  1 |         A |   John Smith |     Sally Bosh | 101 |         A |  Sally Bosh |      TypeA |
    |  4 |         B |   Joe Sample |    George Test | 105 |         B | George Test |      TypeF |

这是我目前的收获

select a.*, b.*
from sourcelist a 
join targetjoin b
on a.groupname=b.groupname
and 
case
when b.recordtype in ('TypeA') and a.referredbyname=b.name then 1
when b.recordtype in ('TypeB') and a.referredbyname=b.name then 2
when b.recordtype in ('TypeC') and a.referredbyname=b.name then 3
when b.recordtype in ('TypeD') and a.referredbyname=b.name then 4
when b.recordtype in ('TypeE') and a.referredbyname=b.name then 5
when b.recordtype in ('TypeF') and a.referredbyname=b.name then 6
else 0
end in (1,2,3,4,5,6)
order by a.groupname

架构:http://sqlfiddle.com/#!9/eb97f

感谢您的帮助!

Answer 1

SELECT x.* 
  FROM targetjoin x
  JOIN
     ( SELECT name
            , MIN(recordtype) recordtype 
         FROM targetjoin 
        GROUP 
           BY name
     ) y
    ON y.name = x.name
   AND y.recordtype = x.recordtype;

这个问题的最后一部分留给读者作为练习。