MySql更新表排名两种方式

IYBin 331 2019-05-31

并列排名

UPDATE test0 a,
(
	SELECT
		id,
		num,
		( SELECT COUNT( * ) + 1 FROM test0 c WHERE c.num > test0.num ) pm 
	FROM
		test0 
	) b 
	SET a.rank = b.pm 
WHERE
	a.id = b.id

行号排名

UPDATE test0 a
LEFT JOIN (
	SELECT
		( @rno := @rno + 1 ) AS pm,
		id 
	FROM
		( SELECT id FROM test0 ORDER BY num DESC ) c,
		( SELECT @rno := 0 ) d 
	) b ON b.id = a.id 
SET a.rank = b.pm

# MySql