我有一个查询如下:
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;