如何加入可选的子表?

我想从下表中选择数据。 (AWS RDS,无服务器Aurora MySQL)

表1用户

| id | name    |
|----|---------|
|  1 | Alpha   |
|  2 | Bravo   |
|  3 | Charlie |

* id is PK.

表2头像

| id | user_id | image_path |
|----|---------|------------|
|  1 |       1 | 1.jpg      |
|  2 |       2 | 2.jpg      |
|  3 |       1 | 1-2.jpg    |

* id is PK.
* user_id is FK user.id
  • 用户可以选择上传头像图片。
  • 用户无法删除头像。仅追加。

期望

  • 如果存在头像,它将选择id的最大值。
  • 如果不存在,则选择NULL。
| user_id | user_name | avatar_id | avatar_image_path |
|---------|-----------|-----------|-------------------|
|       1 | Alpha     |         3 | 1-2.jpg           |
|       2 | Bravo     |         2 | 2.jpg             |
|       3 | Charlie   | NULL      | NULL              |

现在,我编写了此SQL。

SELECT
  user.id AS user_id,
  user.name AS user_name,
  avatar.id AS avatar_id,
  avatar.image_path AS avatar_image_path
FROM user
LEFT JOIN avatar ON avatar.user_id = user.id
GROUP BY user.id
HAVING MAX(avatar.id);

but, row of user_id = 3 was not selected.

输出量

| user_id | user_name | avatar_id | avatar_image_path |
|---------|-----------|-----------|-------------------|
|       1 | Alpha     |         3 | 1-2.jpg           |
|       2 | Bravo     |         2 | 2.jpg             |

我应该怎么做?

评论
  • 带我飞
    带我飞 回复

    您可以在脚本下方尝试以下操作-

    SELECT A.*,C.image_path
    FROM user A
    LEFT JOIN (
        SELECT  user_id,max(id) id
        FROM  avatar
        GROUP BY user_id
    ) B ON A.id = B.user_id
    LEFT JOIN avatar C ON B.id = C.id