sql - 避免多个列的单个表的多个左连接

Question

模拟我面临的问题的表格和数据:

create table table1 (people varchar(10), special_id varchar(20));
create table table2 (special_id varchar(20), dependent_value int8, value_wanted varchar(5));

insert into table1 values 
('person1', 'abc'),
('person1', 'abc'),
('person1', 'abc'),
('person1', 'abc'),
('person1', 'bbb'),
('person1', 'bbb'),
('person1', 'ccd');


insert into table2 values
('abc', '02', 'boom'),
('abc', '01', 'zoom'),
('bbb', '01', 'woom'),
('abc', '03', 'whom');

这是我想要达到的结果:

+---------+------------+---------+---------+
| people  | special_id | code_01 | code_02 |
+---------+------------+---------+---------+
| person1 | abc        | zoom    | boom    |
| person1 | abc        | zoom    | boom    |
| person1 | abc        | zoom    | boom    |
| person1 | abc        | zoom    | boom    |
| person1 | bbb        | woom    | NULL    |
| person1 | bbb        | woom    | NULL    |
| person1 | ccd        | NULL    | NULL    |
+---------+------------+---------+---------+

使用模型表，我可以通过执行以下操作创建上面的表:

select t1.*, t2.value_wanted as code_01, t2_1.value_wanted as code_02
from table1 t1
left join table2 t2
on t2.special_id = t1.special_id and t2.dependent_value = '01'
left join table2 t2_1
on t2_1.special_id = t1.special_id and t2_1.dependent_value = '02';

问题是为了添加 code_03 和其他列，我必须不断添加左连接。这似乎不是很有效。考虑到性能，有没有更好的方法来做到这一点？

Answer 1

您还可以使用聚合:

select t1.*, t2.code_01, t2.code_02, t2.code_03
from table1 t1 left join table2
     (select t2.special_id,
             max(case when t2.dependent_value = '01' then t2.value_wanted end) as code_01,
             max(case when t2.dependent_value = '02' then t2.value_wanted end) as code_02,
             max(case when t2.dependent_value = '03' then t2.value_wanted end) as code_03
      from table2 t2
      group by t2.special_id
     ) t2
     on t2.special_id = t1.special_id ;

您需要向子查询添加新的条件，而不是新的连接。这是更快还是更慢。 . .好吧，这取决于。