mysql - SQL查询返回错误的结果集

Question

我有这张表:

columns:
Id  product_id name status update_date

1   1   prod1   bought  2016-04-20 10:10:10
2   1   prod1   sold    2016-04-22 12:25:00
3   1   prod1   sold    2016-06-03 09:42:15

我想执行这个查询:

select id,name,status,max(update_date) from product group by name,status;

我得到:

1   prod1   bought  2016-04-20 10:10:10
2   prod1   sold    2016-06-03 09:42:15

对于结果集中的第二行，我必须得到:

3   prod1   sold    2016-06-03 09:42:15

而不是:2 prod1 已于 2016-06-03 09:42:15 出售!

Answer 1

试试这个；)

select id,name,status,update_date from product
where (name, status, update_date) in (
    select name,status,max(update_date) from product
    group by name,status
)

或者

select t1.id, t1.name, t1.status, t1.update_date
from product t1
inner join (
    select name,status,max(update_date) as update_date from product
    group by name,status
) t2 on t1.name = t2.name and t1.status = t2.status and t1.update_date = t2.update_date