我正在尝试为构建一个数据库(下面是sqlfiddle) Items
(假设它们是任何类型的机器,在本例中是汽车和自行车),它们可能必须在给定的时间间隔内与一台或多台机器进行检查 CheckTypes
.
一 Item
有很多 ItemsCheckTypes
: Item
car1必须每两年在车库进行一次检查,每年进行一次目视检查 Item
car2只需在车库检查 Item
自行车2根本不需要检查。
一 Check
属于 CheckType
而且有很多 ItemsChecks
:
一 Check
2018年1月1日 CheckType
“车库检查”,检查了car1和car2( ItemsChecks
完成作业)
我现在想要的是一个查询,找出最后一次 Item
已经检查了每一个 ItemsCheckTypes
. 预期结果如下:
Items.id | CheckTypes.id | LAST CHECK.date | LAST CHECK.id
1 | 1 | 2018-01-01 | 4
1 | 2 | 2017-08-01 | 3
2 | 1 | 2016-01-01 | 1
我尝试了许多连接、子查询和其他方法,试图找出哪个表是我的起点(我正在考虑 ItemsChecks
或者 Items
)-但现在我只是不知道这是否有可能,或者我应该做下面的一个来(希望)简化它:
创建某种缓存/帮助程序表,其中存储上次检查的id、日期和类型\u id-如果日期大于当前的上次日期,则在新的检查分配时被覆盖(可能是新的源关闭错误)
提出更好的数据库设计
为每个项目做一个新的查询(我认为这可能会更容易,但不会给我一个选项来查找所有需要检查的项目)
在应用程序级别,我使用的是cakephp3.x,但我怀疑cake解决方案会比sql解决方案简单。模型的关联看起来很好,其他的一切都在按预期工作。
最后是sql小提琴:http://sqlfiddle.com/#!2017年9月b3d1f8日
任何帮助或建议都很感激,我不知道该怎么办。。。
1条答案
按热度按时间dgjrabp21#
检查分组依据
item_id
以及check_type_id
获取最新日期:select
c2.item_id,
c2.check_type_id,
c2.latest last_check,
c1.id check_id
from checks c1
inner join (
select ic.item_id, c.check_type_id, max(c.date) latest
from items_checks ic
inner join checks c on c.id = ic.check_id
group by ic.item_id, c.check_type_id
) c2 on c2.check_type_id = c1.check_type_id and c2.latest = c1.date
order by
c2.item_id,
c2.check_type_id
;