mysql - 如何在此查询中选择最大日期？

Question

我想编写一个查询来检索每个用户的名称、ID 和上次修改日期。以下查询给出了表 UserDetails1 和 UserDetails2 中的名称、ID 和上次修改日期。

如何修改此查询以返回单个日期值，即任一详细信息表中给定 user_id 的最大日期？

SELECT
    id,
    name,
    MAX(userdetails1.date_modified),
    MAX(userdetails2.date_modified)
FROM User user
INNER JOIN UserDetails1 userdetails1
    ON userdetails1.user_id = user.id
INNER JOIN UserDetails2 userdetails2
    ON userdetails2.user_id = user.id


User
id | name
---------
1  | name1
2  | name2
3  | name3

UserDetails1
user_id | date_modified
---------------------
1   | 2016-11-28 16:28:26
....

UserDetails2
user_id | date_modified
---------------------
1   | 2016-11-29 16:29:26
....

Answer 1

尝试一下这个，尽管我认为可以有更优化的编写方式。

SELECT
    id,
    name,
    (CASE 
        WHEN MAX(userdetails1.date_modified) > MAX(userdetails2.date_modified) 
        THEN MAX(userdetails1.date_modified)
        ELSE MAX(userdetails2.date_modified)
    END) 
FROM User user
INNER JOIN UserDetails1 userdetails1
    ON userdetails1.user_id = user.id
INNER JOIN UserDetails2 userdetails2
    ON userdetails2.user_id = user.id
GROUP BY id, name