php - 如何在php中获取最大日期数据

Question

我有三个表..

SEEKER
seeker_nic--- username---request_fulfilled
111-----------ali--------YES
222-----------bilal------YES

第二个表是

DONOR
donor_nic---username----area
999----------Fahad-------UK
555----------SAJAD------USA
777---------HAMZA-------PK

第三张表是

STATUS
status-id---seeker_nic---donor_nic--requestfulfilled_by----request_fulfilled_date 
1 -------------111-------999---------- Fahad-------------2012/04/09
2 -------------111-------555---------- SAJAD-------------2012/05/15
3--------------222------777-----------HAMZA--------------2012/07/20

现在我想要这个带有最新数据的 SEEKER (111) 结果..

seeker_nic---username--- request_fulfilled---requestfulfilled_by----area---request_fulfilled_date
111-----------ali--------YES-----------------SAJAD-----------------USA--------2012/05/15

我正在尝试这个查询，这个查询显示了 rite seeker_nic 和 requestfulfilled_date，但是它显示了错误的 donor-nic、area 和 requestfulfilled_by...

SELECT seeker.seeker_nic, donor.donor_nic, donor.area, 
status.requestfulfilled_b , max( status.`request_fulfilled_date` ) AS request_fulfilled_date
FROM seeker
JOIN STATUS ON seeker.seeker_nic = status.seeker_nic
JOIN DONOR ON status.donor_nic = donor.donor_nic
WHERE seeker.username = '$uname'
GROUP BY status.`seeker_nic` 

我得到这样的回答....

seeker_nic---username--- request_fulfilled---requestfulfilled_by--------area--------request_fulfilled_date
111-----------ali---------------YES-----------------HAMZA--------------PK------------2012/05/15

请帮助我.. :(

Answer 1

试试这个:

SELECT seeker.seeker_nic, donor.donor_nic, donor.area, status.requestfulfilled_by, status.request_fulfilled_date
FROM seeker
JOIN (
  SELECT seeker_nic, max(request_fulfilled_date) as last_date
  FROM status
  GROUP BY seeker_nic
) x ON x.seeker_nic = seeker.seeker_nic
JOIN STATUS 
  ON x.seeker_nic = status.seeker_nic
  AND x.last_date = status.request_fulfilled_date
JOIN DONOR 
  ON status.donor_nic = donor.donor_nic
WHERE seeker.username = '$uname'