mysql - 如何从最大日期子查询列表中选择最大日期

Question

我需要从不同表中的一堆不同日期中获取最大日期。我有一个查询返回 6 个日期。我需要以某种方式获得该查询的最大值。这是我的第一个有效查询，它为我提供了 6 个不同的最大日期:

    SELECT  
        MAX(mi.last_updated_dts),
        (SELECT MAX(lt.created_dts) FROM live_training as lt WHERE lt.me_id = 1156),
        (SELECT MAX(gm.created_dts) FROM group_member as gm WHERE gm.me_id = 1156),
        (SELECT MAX(clm.created_dts) FROM contact_list_member as clm WHERE clm.me_id = 1156),
        (SELECT MAX(mc.created_dts) FROM member_case as mc WHERE mc.me_id = 1156),
        (SELECT MAX(mcc.created_dts) FROM member_case_comment as mcc INNER JOIN member_case as mc ON (mcc.member_case_id = mc.id) WHERE mc.me_id = 1156)
    FROM 
        member_info AS mi
    WHERE mi.id = 1276

这是我正在尝试做的事情，但它不起作用:

SELECT MAX(
             SELECT  
                  MAX(mi.last_updated_dts),
                  (SELECT MAX(lt.created_dts) FROM live_training as lt WHERE lt.me_id = 1156),
                  (SELECT MAX(gm.created_dts) FROM group_member as gm WHERE gm.me_id = 1156),
                  (SELECT MAX(clm.created_dts) FROM contact_list_member as clm WHERE clm.me_id = 1156),
                  (SELECT MAX(mc.created_dts) FROM member_case as mc WHERE mc.me_id = 1156),
                  (SELECT MAX(mcc.created_dts) FROM member_case_comment as mcc INNER JOIN member_case as mc ON (mcc.member_case_id = mc.id) WHERE mc.me_id =1156)
             FROM 
                  member_info AS mi
             WHERE mi.id = 1276
          ) as theMostMaxDate

我不知道应该在 from 语句中添加什么内容，也不知道这是否可能。感谢您的帮助!

Answer 1

一种解决方案可能是重写查询以在派生表中使用union all并从中获取最大值，尽管这只会为您提供一个日期，而不会提供有关其来源的信息(但如果这很重要，您可以添加一个带有文字值的列，指示哪个查询为您提供了最大日期):

SELECT MAX(max_date) 
FROM (
    SELECT MAX(mi.last_updated_dts) AS max_date FROM member_info AS mi WHERE mi.id = 1276
    UNION ALL
    SELECT MAX(lt.created_dts) FROM live_training as lt WHERE lt.me_id = 1156
    UNION ALL
    SELECT MAX(gm.created_dts) FROM group_member as gm WHERE gm.me_id = 1156
    UNION ALL
    SELECT MAX(clm.created_dts) FROM contact_list_member as clm WHERE clm.me_id = 1156
    UNION ALL
    SELECT MAX(mc.created_dts) FROM member_case as mc WHERE mc.me_id = 1156
    UNION ALL
    SELECT MAX(mcc.created_dts) FROM member_case_comment as mcc 
    INNER JOIN member_case as mc ON (mcc.member_case_id = mc.id) WHERE mc.me_id = 1156
) t