使用子表中的最新条目对mySQL记录进行分组

Question

我有三桌办公室，电脑和维护。办公室只是办公室的列表，电脑属于办公室以及有很多维护。

我想只使用Maintain 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