mysql :=和=的区别及统计查询结果行号

变量初始化：@rownum:=0

累加，标记行号：@rownum:=@rownum+1

SELECT
	rank
FROM
	 (
		SELECT
			id,
			@rownum := CAST(@rownum AS SIGNED) + 1 AS rank 
		FROM
			(
				SELECT
					c.id,
					bcu.guid,
					COUNT(DISTINCT bcu.uguid) AS num 
				FROM
					`b_record` bcu 
				JOIN b_company c ON bcu.cguid = c.cguid 
				WHERE
					bcu.updatetime BETWEEN (NOW() - INTERVAL 148 HOUR)
				AND NOW()
				AND c.state = 1 
				GROUP BY
					cguid 
				ORDER BY
					num DESC
			) s,
			(SELECT @rownum := 0) r
	) t 
WHERE
	id = 123

完整代码如上图所示，其中

SELECT
					c.id,
					bcu.companyguid,
					COUNT(DISTINCT bcu.userguid) AS num 
				FROM
					`b_company_user_visit_record` bcu 
				JOIN b_company c ON bcu.companyguid = c.companyguid 
				WHERE
					bcu.updatetime BETWEEN (NOW() - INTERVAL 148 HOUR)
				AND NOW()
				AND c.state = 1 
				GROUP BY
					companyguid 
				ORDER BY
					num DESC

上面一段查出结果

SELECT
			id,
			@rownum := CAST(@rownum AS SIGNED) + 1 AS rank 
		FROM
			(
					) s,
			(SELECT @rownum := 0) r

上面一段添加行号，因为在java中，所以合并成一条语句，而在sql中可分开写，先定义rownum变量，再添加行号。

参考：

https://blog.youkuaiyun.com/wangjun5159/article/details/51378558

https://blog.youkuaiyun.com/u012102536/article/details/59112054