从每个表中包含重复值的5个表中选择非dupilcate值。

Question

报表包含来自5个表数据的数据。但是每个表都包含重复的值，并将每个值返回3-5次。我可以在开始时使用不同的ID，但是report不包含ID列。如何从所有表中选择非重复值？

Select t1.name, t3.address, t4.phone,
case when t2.works is not null then 'Y' else 'N' end as employee,
case when t5.resident is not null then 'Y' else 'N' end as Resident

from table1 t1
inner join table2 t2 on t2.ID=t1.ID
inner join table3 t3 on t3.ID=t2.ID
....
inner join table5 t5 on t5.ID=t2.ID;

Answer 1

解决问题的一种方法是使用CTE的

with t1 as (
        select distinct <colnames...>
        from table1 
), t2 as (
        select distinct <colnames...>
        from table1
), .
   .
   .
select <columns>
from t1
inner join t2 on t2.ID = t1.ID
.
.
.