针对来自多个ID的单一身份的SQL解决方案

我正在寻找一种通过SQL的方法,该方法从共享公用(但不同)键的行中建立单个visitor_id,然后生成将已建立的visitor_id分配回所有行的输出表。

例:

with things as (
    select 'aaa' as thing_id, '111' as visitor_id, 'alpha@gmail.com' as email_address, '(111) 111-1111' as phone_number
    union all select 'bbb' as thing_id, '111' as visitor_id, 'beta@gmail.com' as email_address, null as phone_number
    union all select 'ccc' as thing_id, '111' as visitor_id, null as email_address, '(222) 222-2222' as phone_number
    union all select 'ddd' as thing_id, '222' as visitor_id, 'alpha@gmail.com' as email_address, '(333) 333-3333' as phone_number
    union all select 'eee' as thing_id, '333' as visitor_id, 'alpha@gmail.com' as email_address, null as phone_number
    union all select 'fff' as thing_id, '444' as visitor_id, 'charlie@gmail.com' as email_address, '(333) 333-3333' as phone_number
    union all select 'ggg' as thing_id, '555' as visitor_id, 'charlie@gmail.com' as email_address, '(444) 444-4444' as phone_number
    union all select 'hhh' as thing_id, '666' as visitor_id, null as email_address, '(444) 444-4444' as phone_number

)

我知道所有这些东西都属于同一个vistior_id

  • 事物aaa,bbb和ccc都共享相同的visitor_id
  • 东西ddd和eee与aaa,bbb和ccc共享相同的email_address
  • 事物fff与事物ddd共享相同的phone_number(因此与aaa具有相同的vistior_id)
  • ggg与fff具有相同的email_address(因此与fff具有相同的vistior_id,与aaa具有相同的visitor_id)
  • 事物hhh与ggg共享相同的phone_number(因此与ggg相同的vistior_id与aaa相同)

我想要的输出表将是:

|---------------------|------------------|
|      thing_id       |     vistior_id   |
|---------------------|------------------|
|         aaa         |        111       |
|---------------------|------------------|
|         bbb         |        111       |
|---------------------|------------------|
|         ccc         |        111       |
|---------------------|------------------|
|         ddd         |        111       |
|---------------------|------------------|
|         eee         |        111       |
|---------------------|------------------|
|         fff         |        111       |
|---------------------|------------------|
|         ggg         |        111       |
|---------------------|------------------|
|         hhh         |        111       |
|---------------------|------------------|

我尝试过交叉联接,然后消除重复项,但是,我一直在与fff,ggg和hhh进行斗争,因为它们仅通过其他事物与事物aaa相关

评论