mysql:从联接表中获取每个最新条目

qnakjoqk  于 2021-06-23  发布在  Mysql
关注(0)|答案(1)|浏览(337)

我正在尝试为构建一个数据库(下面是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日
任何帮助或建议都很感激,我不知道该怎么办。。。

dgjrabp2

dgjrabp21#

检查分组依据 item_id 以及 check_type_id 获取最新日期:

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
;
``` `check_id` 还是不见了。所以我们取前面的select并将其与表连接起来 `checks` 为了得到它。

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
;

相关问题