MySQL Group by a field based on another field maximum value of another field IN THE GROUP(不在表中)

Question

考虑下表:

un_id   avl_id  avl_date    avl_status
1738    6377398 2011-03-10  unavailable 
1738    6377399 2011-03-11  unavailable 
1738    6377400 2011-03-12  unavailable 
1738    6719067 2011-03-12  unavailable
1738    6719351 2011-03-12  available
1738    6377401 2011-03-13  unavailable 
1738    6377402 2011-03-14  unavailable 
1738    6377403 2011-03-15  unavailable 
1738    6377404 2011-03-16  available
1738    6719068 2011-03-16  unavailable 
1738    6719352 2011-03-16  available

这是从以下查询中获得的:

SELECT 
    tbl_unit.un_id,
    tbl_availability.avl_id,
    tbl_availability.avl_date, 
    tbl_availability.avl_status
FROM
    tbl_unit
INNER JOIN 
    tbl_availability ON 
    tbl_unit.un_id = tbl_availability.un_id
WHERE
    tbl_availability.avl_active='True' AND
    tbl_unit.un_active='True' AND
    tbl_availability.avl_date >= '2011-03-10' AND
    tbl_availability.avl_date 



<p>What I want is to GROUP BY un_id so that only the avl_status having the highest avl_id is displayed. i.e:</p>



un_id   avl_id  avl_date    avl_status
1738    6377398 2011-03-10  unavailable 
1738    6377399 2011-03-11  unavailable 
1738    6719351 2011-03-12  available
1738    6377401 2011-03-13  booked 
1738    6377402 2011-03-14  booked 
1738    6377403 2011-03-15  booked 
1738    6719352 2011-03-16  available

我尝试添加 GROUP BY 和 HAVING 子句以及各种子查询，但每次都失败了....

感谢所有帮助! :)- 亚当。

Answer 1

SELECT 
    tbl_unit.un_id,
    tbl_availability.avl_id,
    tbl_availability.avl_date, 
    tbl_availability.avl_status
FROM
    tbl_unit
INNER JOIN 
    (select un_id, max(avl_id) as max_avl_id from tbl_availability group by un_id) T
    on tbl_unit.un_id = T.un_id
INNER JOIN tbl_availability ON 
    T.max_avl_id = tbl_availability.avl_id
WHERE
    tbl_availability.avl_active='True' AND
    tbl_unit.un_active='True' AND
    tbl_availability.avl_date >= '2011-03-10' AND
    tbl_availability.avl_date