php - mySQL 组记录具有来自子表的最新条目

Question

我有 3 个 table 办公室、计算机和维护。办公室只是办公室的列表，计算机属于办公室，也有许多维护。

我想将所有表与维护表中的最新条目左连接。 下面的代码有效，但它只是将维护表中最旧的条目分组。

SELECT `Computer`.`id`, `Computer`.`control`, `Computer`.`operator`, `Computer`.`datePurchased`, `Computer`.`type`, `Computer`.`property`, `Computer`.`printer`, `Computer`.`scanner`, `Computer`.`osx`, `Computer`.`applications`, `Computer`.`licence`, `Computer`.`isStandAlone`, `Computer`.`isInternet`, `Computer`.`isNetwork`, `Computer`.`generalStatus`, `Computer`.`ip_address`, `Computer`.`mac_address`, `Computer`.`user_id`, `Computer`.`office_id`, `Computer`.`created`, `Computer`.`modified`, `Computer`.`deleted`, `Office`.`id`, `Office`.`description`, `Office`.`main_office`, `Maintain`.`id`, `Maintain`.`dateEncoded`, `Maintain`.`findings`, `Maintain`.`checkedBy`, `Maintain`.`remarks`, `Maintain`.`computer_id`, `Maintain`.`created`, `Maintain`.`modified`, `Maintain`.`user_id` FROM `computers`.`computer` AS `Computer`

LEFT JOIN `computers`.`office` AS `Office` 
ON (`Office`.`id` = `Computer`.`office_id`)

LEFT JOIN `computers`.`maintain` AS `Maintain`
ON (`Computer`.`id` = `Maintain`.`computer_id`)

LEFT JOIN (SELECT MAX(dateEncoded) maxDate, findings FROM maintain GROUP BY computer_id) AS `P2`
ON (`Maintain`.`dateEncoded` = `p2`.`maxDate`) 

WHERE `Office`.`main_office` LIKE '%CVPH MON%'
GROUP BY `Computer`.`id` 
ORDER BY `Office`.`description` ASC

样本

OFFICE
1    AAAA
2    BBBB

COMPUTER
id   name   office_id
1    CP1    1
2    CP2    1
3    CP3    2

Maintain
id   description   date        computer_id
1     Fix         06/20/2014    1
2     Fix         06/11/2014    1
3     Fix         06/12/2014    2
4     Fix         06/15/2014    2


Result if query on computer=CP1 should be
Office     Computer_name    Maintain_desc    Date
AAA         CP1               Fix           06/20/2014   <- Latest entry in maintain

Answer 1

你可以这样做

SELECT `c`.`id`, 
`c`.`name`, 
`c`.`office_id` ,
`o`.`name` office_name, 
`m`.`date`,
`m`.`description`
FROM `computer` AS c
LEFT JOIN `office` AS `o` 
ON (`o`.`id` = `c`.`office_id`)
LEFT JOIN `maintain` AS m
ON (`c`.`id` = `m`.`computer_id`)
INNER JOIN 
(SELECT computer_id,MAX(`date`) maxdate 
 FROM maintain 
 GROUP BY computer_id ) t
ON(m.`date`=t.maxdate AND m.computer_id= t.computer_id)
 WHERE `c`.`name` ='CP1' ... more conditions

Demo