sql - 如何在不重复的情况下在 SQL 查询中连接两个表

Question

User_Master (ID is Primary Key   identity)

ID      Name    Age      Gender
1       James   25       Male
2       John    30       Male

User_Image (User_Id is foreign key from User_Master)

ID     User_Id   Thumbnail
1         2       ABC.Jpeg
2         2       AB.jpeg

我需要从 User_Master 获取所有字段和一个来自 User_Image 的所选用户的缩略图名称 table

像这样 :

User_Id    Name         Age      Gender Thumbnail
1          James        25       Male   null
2          John         30       Male   ABC.Jpeg

Answer 1

一种方式，假设您想要具有最低 id 的图像，因为显然有多个选项；

select 
    id, name, age, gender, thumbnail 
from (
    select
        rank() over (partition by m.id order by i.id asc) as rank,
        m.id,
        name, 
        age, 
        gender, 
        thumbnail
    from
        User_Master m left join User_Image i on (i.user_id = m.id)
) t where rank = 1