doupingpeng7567
doupingpeng7567
2012-04-10 19:46
浏览 101
已采纳

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

i have three tables..

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

second table is

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

third table is

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

now i want this result for SEEKER (111) with latest data..

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

i am trying this query, this query shows rite seeker_nic, and requestfulfilled_date, but it shows wrong donor-nic, area and 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` 

i am getting ans like this....

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

plz help me.. :(

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dqc18251
    dqc18251 2012-04-10 20:19
    已采纳

    Try this:

    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'
    
    点赞 评论
  • douhui8163
    douhui8163 2012-04-10 20:27
    SELECT x.seeker_nic, y.username, y.request_fulfilled,
           x.requestfulfilled_by, z.area, x.request_fulfilled_date
    FROM status x, seeker y, donor z
    WHERE x.seeker_nic=y.seeker_nic AND y.seeker_nic=111 AND z.donor_nic=x.donor_nic;
    
    点赞 评论
  • douzhang1955
    douzhang1955 2012-04-10 20:35

    If you need to select latest date for one particular user, you do not need a GROUP BY clause:

    SELECT
        status.request_fulfilled_date, # status.requestfulfilled_by,
        seeker.seeker_nic, seeker.username, seeker.request_fulfilled,
        donor.donor_nic, donor.username, donor.area 
    FROM      status
    LEFT JOIN seeker ON status.seeker_nic = seeker.seeker_nic
    LEFT JOIN donor  ON status.donoc_nic  = donor.donor_nic
    WHERE seeker.username = 'ali'
    ORDER BY status.request_fulfilled_date DESC
    LIMIT 1
    
    点赞 评论

相关推荐