我正在寻找一种通过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相关
