LEFT JOIN子查询仅返回单个患病用户问题排查求助

问题描述 #

我有一份员工列表，通过LEFT JOIN users_ill表判断用户是否患病。现在遇到的问题是：数据库里有多名health_at字段为NULL的患病用户，但查询结果只显示一名；调整排序规则后，显示的患病用户会变，但始终只能显示一名。请问我的SQL语句哪里错了？

Users Ill表结构 #

id | int
user_id | uuid
health_at | timestamp null
created_at | timestamp

原SQL代码 #

SELECT 
 u.id as user_id, u.firstname, u.lastname, u.created_at,
 ui.created_at as ill_since
 FROM users u

 LEFT JOIN (
   SELECT user_id, created_at FROM users_ill WHERE health_at IS NULL ORDER BY created_at DESC 
   LIMIT 1
 ) ui ON (ui.user_id = u.id)

WHERE u.tenant_id = $1 AND u.deleted_by IS NULL
ORDER BY u.firstname ASC LIMIT 50 OFFSET $8;

问题根源 #

你的子查询里加了LIMIT 1，这个限制会让子查询只返回单条符合条件的记录——也就是所有health_at IS NULL的记录里，created_at最晚的那一条。之后LEFT JOIN只会把这条记录的user_id和用户表匹配，所以最终只有这一个用户能显示ill_since，其他用户的该字段都是NULL。调整排序规则时，子查询返回的单条记录会变，所以显示的患病用户也跟着变，但始终只会有一个。

修复方案 #

根据你的需求，有两种修复方式：

需求1：给每个用户显示最新的一条患病记录（health_at IS NULL） #

去掉子查询的LIMIT 1，改成按user_id分组，取每个用户最新的患病记录时间：

SELECT 
 u.id as user_id, u.firstname, u.lastname, u.created_at,
 ui.ill_since
 FROM users u

 LEFT JOIN (
   SELECT user_id, MAX(created_at) as ill_since 
   FROM users_ill 
   WHERE health_at IS NULL 
   GROUP BY user_id
 ) ui ON (ui.user_id = u.id)

WHERE u.tenant_id = $1 AND u.deleted_by IS NULL
ORDER BY u.firstname ASC LIMIT 50 OFFSET $8;

需求2：显示用户所有的患病记录（注意会产生多行） #

如果需要展示用户的每一条符合条件的患病记录，直接关联整个过滤后的users_ill表即可：

SELECT 
 u.id as user_id, u.firstname, u.lastname, u.created_at,
 ui.created_at as ill_since
 FROM users u

 LEFT JOIN users_ill ui ON ui.user_id = u.id AND ui.health_at IS NULL

WHERE u.tenant_id = $1 AND u.deleted_by IS NULL
ORDER BY u.firstname ASC LIMIT 50 OFFSET $8;

内容的提问来源于stack exchange，提问作者oemer ok