mysql - 从不同日期时间字段中选择最新日期时间

Question

不同的表中有不同的日期时间字段，我必须选择所有日期时间字段中最新的一个。这些表格是:

Question
--------
askedby
questionType
lastupdated

Document
----------
submittedBy
documentType
lastupdated

Instruction
-----------
submittedBy
instructionType
lastupdated

尝试从

开始

SELECT Question.lastupdated,Document.lastupdated,Instruction.lastupdated
FROM Question,Document,Instruction
WHERE lastupdated=(select max(lastupdated))
ORDER BY lastupdated DESC

如果表中lastupdated 的值为:

Question table:    2015-06-28 07:00:00
Document table:    2015-06-28 07:30:00
Instruction table: 2015-06-28 08:00:00

然后应选择Instruction表中的值，例如2015-06-28 08:00:00

这里我必须从三个数据库中选择最新的lastupdated 列。我是 SQL 查询新手，不知道如何开始。

Answer 1

因为看起来您只想从三个分离集中提取最后一个日期，所以一个简单的选项是对每个表使用查询并使用union all从三个结果中生成一组并将 max 应用到该集合，如下所示:

SELECT MAX(lastupdated) AS max_lastupdated
FROM 
(
    SELECT MAX(lastupdated) lastupdated FROM Question
    UNION ALL
    SELECT MAX(lastupdated) lastupdated FROM Document
    UNION ALL
    SELECT MAX(lastupdated) lastupdated FROM Instruction
) A

实际上，您甚至不需要在三个 select 语句中选择最大值，只需获取 lastupdated 就足够了，尽管如果有索引支持，上面的查询可能会更快:

SELECT MAX(lastupdated) AS max_lastupdated
FROM 
(
    SELECT lastupdated FROM Question
    UNION ALL
    SELECT lastupdated FROM Document
    UNION ALL
    SELECT lastupdated FROM Instruction
) A

Sample SQL Fiddle