获取表的最高薪资和匹配日期。

Question

branch_id   Date           total_amount
9           12-01-2016       1000
7           12-02-2016       2000
5           22-01-2016       3000
6           24-02-2016       4000

SELECT `branch_id`, `bill_date`, MAX(`bill_amount_total`) FROM `pos_master`  group by `branch_id` 

如果我使用这个查询，我得到了bill_amount_total的最大值，但是日期不匹配

Answer 1

select m.*
from pos_master m 
join
(
    SELECT branch_id, MAX(bill_amount_total) as max_amount
    FROM pos_master  
    group by branch_id
) tmp on tmp.branch_id = m.branch_id 
     and tmp.max_amount = m.bill_amount_total 

Answer 2

这个会帮你的

SELECT `branch_id`, `bill_date`,`bill_amount_total` FROM `pos_master`ORDER BY bill_amount_total DESC LIMIT 1