数据库表:
服务(id,name)
history(id,service_id,status,message,smessage,date),其中status是从0到4的整数。
我正试图根据每个不同service_id的历史来计算出该服务的当前状态。当计划维护时,它会与未来的开始和结束时间一起输入数据库,直到现在,我一直在使用它来计算状态:
SELECT
d. NAME,
d.id,
c.date,
COALESCE (c.smessage, 'Normal') AS smessage,
COALESCE (c. STATUS, 0) AS STATUS
FROM
services d
LEFT JOIN (
SELECT
*
FROM
history
WHERE
id IN (
SELECT
max(id)
FROM
history
WHERE
date < now()
GROUP BY
service_id
)
) c ON d.id = c.service_id;
然而,当存在重叠维护时,或者当提前两周安排了一次维护,并且在接下来的15分钟内安排了另一次维护时,第二次维护不会正确显示。
样本数据
# id service_id date status message added_by smessage
126 19 2014-10-21 11:32:34 0 Regular Status update. usr1 Regular update.
125 19 2014-10-23 22:00:00 0 Maint1 done. usr1 Maint1Done.
124 19 2014-10-23 17:00:00 3 Maint2 done. usr1 Maint2 done.
123 19 2014-10-21 22:00:00 0 Maint3 done. usr1 Maint3 done
122 19 2014-10-21 17:00:00 3 Maint3 Sched. usr1 Maint3 sched
121 19 2014-10-20 22:00:00 0 Maint2 sched. usr1 maint2 sched
120 19 2014-10-20 17:00:00 3 Maint1 sched. usr1 Maint1 sched
您有正确的基本结构,只需要使用date
而不是id
:
SELECT s.NAME, s.id, s.date,
COALESCE(h.smessage, 'Normal') AS smessage,
COALESCE(h.STATUS, 0) AS STATUS
FROM services s LEFT JOIN
(SELECT h.*
FROM history
WHERE NOT EXISTS (SELECT 1
FROM history h2
WHERE h2.service_id = h.service_id and
h2.date < now() and
h2.date > h.date
)
) h
ON s.id = h.service_id;
not exists
说:"给我一个没有后续历史记录的给定服务的历史记录。"这相当于得到最后一个。