Group-wise max- hierarchical数据查询帮助

Question

这个问题的第一部分昨天在这里得到了一些很好的帮助，但我今天一直在努力完成我需要的查询。我试图根据某些条件从5个连接表中提取多个列，但我希望结果集每个p.id只包含一个不同的“数据条目”(宠物id是数据条目表中的一个外键)-这个data_entry应该是具有该p.id的所有data_entries中的最高编号(即，一个特定的宠物可以有编号为1、2和3的data_entries -我只想要编号3)。我让下面的代码在第一个查询中正常工作，但是我想添加一个额外的子句来检查返回的最大记录的“更新”日期，但是我似乎不知道如何正确地集成这个子句。

任何帮助都将不胜感激：

这个简化的查询可以在3个连接表中正常工作(没有date比较子句)

SELECT `p`.`id`, `o`.`id`, `o`.`email`, MAX(d.number) 
FROM (`pets` AS `p`, `owners` AS `o`, `data_entries` AS `d`) 
WHERE `p`.`owner_id` = `o`.`id` 
AND `p`.`id` = `d`.`pet_id` 
GROUP BY `p`.`id`, `o`.`id`, `o`.`email` 
ORDER BY `d`.`number` DESC 

但是，当我尝试添加下面的日期比较子句和附加表之间的连接时，查询不会对具有最大数字的data_entry的"updated“列执行日期比较，而是会检查最小的数字。

SELECT `p`.`id` AS `pet_id`, `o`.`id` AS `owner_id`, `o`.`email`, MAX(d.number) 
FROM (`pets` AS `p`, `owners` AS `o`, `data_entries` AS `d`, `k_records_owners` AS `kcro`, `k_records` AS `kcr`) 
WHERE `p`.`owner_id` = `o`.`id` 
AND `p`.`id` = `d`.`pet_id` 
AND `p`.`kc_number` = `kcr`.`do_dg_dog_no` 
AND `kcr`.`pa_breeder_no` = `kcro`.`contact_no` 
AND FROM_UNIXTIME(`d`.`updated`, "%Y-%m-%d") <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 25 day), "%Y-%m-%d") 
GROUP BY `p`.`id`, `o`.`id`, `o`.`email` 
ORDER BY MAX(d.number) DESC

EDIT: latest attempt结果'having子句‘中的未知列’d2.update‘

SELECT p.id AS pet_id, o.id AS owner_id, o.email, MAX(d.number) as max_d, d.updated 
FROM (pets AS p, owners AS o, data_entries AS d, data_entries AS d2, kennel_club_records_owners AS kcro, kennel_club_records AS kcr)
WHERE p.owner_id = o.id 
AND p.id = d.pet_id 
AND p.kc_number = kcr.do_dg_dog_no 
AND kcr.pa_breeder_no = kcro.contact_no 
AND d.number = d2.number
GROUP BY p.id, o.id, o.email 
having FROM_UNIXTIME(d2.updated, '%Y-%m-%d') <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 25 day), '%Y-%m-%d') 
ORDER BY max_d DESC

Answer 1

试试这个：

SELECT p.id AS pet_id, o.id AS owner_id, o.email, MAX(d.number) as max_d, d.updated 
FROM (pets AS p, owners AS o, data_entries AS d, data_entries AS d2, kennel_club_records_owners AS kcro, kennel_club_records AS kcr)
WHERE p.owner_id = o.id 
AND p.id = d.pet_id 
AND p.kc_number = kcr.do_dg_dog_no 
AND kcr.pa_breeder_no = kcro.contact_no 
AND d2.number = max_d
AND FROM_UNIXTIME(d2.updated, '%Y-%m-%d') <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 25 day), '%Y-%m-%d') 
GROUP BY p.id, o.id, o.email 
ORDER BY max_d DESC