我有一个查询如下:

SELECT   ch.id, ch.checkname, MAX(ch.CheckDate) AS CheckDate,
         ch.SeverityNumber, md.CheckRef1, md.CheckSubject,
         DATEDIFF(FROM_UNIXTIME( ch.CheckDate),CURDATE()) AS dateFromalarm
FROM     checks ch, model md
WHERE    ch.SysName='MV3R'
AND      ch.environment='Z/OS'
AND      md.CheckName=ch.CheckName
AND      ***ch.CheckDate <=1569785343***
GROUP BY CheckName
ORDER BY ch.CheckName DESC;


该查询应该为具有最新检查日期的每个检查名称返回一行。
checkname不是唯一的,并且表中每个checkname有很多行。
问题是,当我更改粗体部分的数字(ch.CheckDate 有任何想法吗?

最佳答案

让我们尝试隔离您的max(checkdate)以避免对多个列进行分组,我相信您每个checkname只有1条信息。

select ch.id
    , ch.checkname
    , t1.CheckDate
    , ch.SeverityNumber
    , md.CheckRef1
    , md.CheckSubject
    , datediff(from_unixtime(ch.CheckDate),CURDATE()) as dateFromalarm
from
    (select checkname, max(CheckDate)as CheckDate from checks where CheckDate <=1569785343 group by checkname) as t1
join checks ch on ch.checkname = t1.checkname
join model md on md.CheckName=ch.CheckName
where ch.SysName='MV3R'
    and ch.environment='Z/OS'
order by ch.CheckName desc;

10-06 03:09