mysql先分组，在组里进行排名

 今天有个需求，有一个用户申请表，加了字段用来表示什么时候用户做的第几次申请，大概意思就是2022-12-06用户第一次申请 就写入20221206-0001这样。

新加的字段所以要处理一下老数据加一句sql搞定

 

 

 

先按member_id分组排序得到要用的sn_no

SELECT
	a.*, CONCAT(
		a.cdate,
		'-',
		LPAD(a.rank, 4, 0)
	) snno
FROM
	(
		SELECT
			uid,
			member_id,
			DATE_FORMAT(create_time, '%Y%m%d') cdate,
			@rank :=IF (@cmid = member_id ,@rank + 1, 1) rank,
		  @cmid := member_id
	FROM
		member_request
	ORDER BY
		member_id ASC,
		create_time ASC
	) a

 

 然后连表update一下就行了

SET @rank = 0 ,@cmid = 0;
UPDATE member_request mr
LEFT JOIN (
	SELECT
		a.*, CONCAT(
			a.cdate,
			'-',
			LPAD(a.rank, 4, 0)
		) snno
	FROM
		(
			SELECT
				uid,
				member_id,
				DATE_FORMAT(create_time, '%Y%m%d') cdate,
				@rank :=IF (@cmid = member_id ,@rank + 1, 1) rank,
			@cmid := member_id
		FROM
			member_request
		ORDER BY
			member_id ASC,
			create_time ASC
		) a
) b ON mr.uid = b.uid
SET mr.sn_no = b.snno