【Mysql】分组后获取最近的一条数据

分组后获取最近的一条数据
效果如下：
数据情况:

查询情况:

1. 左连接后过滤

SELECT
	* 
FROM
	cert_application t1
	LEFT JOIN cert_application t2 ON t1.cert_application_type = t2.cert_application_type 
	AND t1.create_time < t2.create_time
	where
	t2.cert_application_id is null
  and t1.user_id = '1'

这样也可 ,

SELECT
	* 
FROM
	cert_application t1
	LEFT JOIN cert_application t2 ON t1.cert_application_type = t2.cert_application_type 
	AND t1.create_time > t2.create_time
	where
	t2.cert_application_id is not null
  and t1.user_id = '1'

2. 使用max函数获取需要排序的字段数据后进行连表过滤

SELECT
	user_id,
	cert_application_id 
FROM
	cert_application t1,
	( SELECT max( cert_application_id ) as cid FROM cert_application GROUP BY cert_application_type ) AS t2 
WHERE
	t1.cert_application_id = t2.cid
    AND t1.user_id = '1'

3. 使用子查询，order by 后 limit 99999 很大的数 ，然后再group by。。歪门邪道

	SELECT
    * 
FROM
    ( SELECT * FROM cert_application ORDER BY cert_application_id DESC LIMIT 1000 ) AS t
	WHERE  user_id = '1'
GROUP BY
    cert_application_type

4. 使用开窗函数 mysql8

SELECT
	* 
FROM
	(
	SELECT
		cert_application_id,
		user_id,
		cert_application_type,
		cert_application_status,
		ROW_NUMBER() over ( PARTITION BY cert_application_type ORDER BY create_time DESC ) AS num 
	FROM
		cert_application 
	WHERE
		user_id = '1' 
	) AS t 
WHERE
	num = 1;