如果表中不存在行，则为COALESCE

I have these two tables:

我有这两个表：

users (this has an updated_at column) and user_updates (this has a foreign key user_id to users and a created_at column)

users（这有一个updated_at列）和user_updates（这个用户有一个外键user_id和一个created_at列）

I want to find the latest user update for user so I have this:

我想为用户找到最新的用户更新，所以我有这个：

SELECT  user_id, 
        MAX(COALESCE(user_updates.created_at,users.updated_at)) as last_update
FROM user_updates
INNER JOIN users 
    ON users.id = user_updates.user_id
WHERE user_id IN (<user_id>)
GROUP BY user_id
;

This works as long as there exists user_updates exist for a user_id. However, if there are no user_updates, I would want to return the users.updated_at. How can I change this query?

只要user_id存在user_updates，这就可以工作。但是，如果没有user_updates，我想返回users.updated_at。如何更改此查询？

How can I fix this?

我怎样才能解决这个问题？

2 个解决方案

#1

Seems you need left join instead :

似乎你需要左连接：

SELECT u.id, 
       MAX(COALESCE(up.created_at, u.updated_at)) as last_update
FROM users u LEFT JOIN 
     user_updates up 
     ON u.id = up.user_id
WHERE u.id IN (<user_id>)
GROUP BY u.id;

#2

Although you can probably assume that up.updated_at is created after the u.updated_at, I would write the logic as:

虽然您可以假设在u.updated_at之后创建up.updated_at，但我会将逻辑编写为：

SELECT u.id, 
       COALESCE(MAX(up.updated_at), MAX(u.updated_at)) as last_update
FROM users u LEFT JOIN 
     user_updates up 
     ON u.id = up.user_id
WHERE u.id IN (<user_id>)
GROUP BY u.id;

This makes it clearer that you want the maximum of the user updates date and if that doesn't exist, then the user date.

这使得您希望获得最大的用户更新日期更清楚，如果不存在，那么用户日期。

#1