select * from
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY create_time DESC) AS rowNum
FROM 表A as c
) d where d.rowNum<=1
这条sql的意思是根据id进行分区,并以create_time进行倒序排序,查出每个id对应的记录中创建时间(create_time)最新的1条记录。
二次用不了了,发现这方法在mysql5上用不了。
SELECT
( @i := CASE WHEN @id= idTHEN @i + 1 ELSE 1 END ) AS rownum,
p.*,
( @id := id )
FROM
表a p,(SELECT@i := 0) AS c
GROUP BY
id,
pk(主键名,因为不加会触发sql_mode=only_full_group_by报错)
HAVING
rownum <= 1
ORDER BY
id,
create_time DESC