sql - 如何在列的不同值上联接两个表？

Question

SELECT table1.*
    ,address
    ,job
FROM table1
JOIN table2 ON table2.name = table1.name

上面的查询也返回 name的重复值的结果。如何将查询转换为 name列的不同值仅获得一个值？

我正在使用SQL Server

Answer 1

您可以使用row_number窗口函数轻松完成此操作。请参阅下面的查询:

select t1.id, t1.name, t1.pets, t2.address, t2.job
from (
    select *,
           row_number() over (partition by [name] order by id) rn 
    from Table1
) t1 
join table2 t2 on t1.name = t2.name
where t1.rn = 1