MySQL给查询结果添加一表表示行号或名次（2）

2019独角兽企业重金招聘Python工程师标准>>>

给关联查询结果添加一列表示名次（order_no）和行号（row_no），并查询出指定条件记录

需求信息

在捐赠和报名系统里，用户直接捐赠和报名的费用都会捐赠给用户指定的慈善机构。但，每个用户只有第一笔捐赠记录会累计到慈善积分，之后的捐赠不算做慈善积分。在系统里为慈善机构有个慈善积分排行榜，每个捐赠/报名用户可以在慈善机构排行榜上看到自己、自己邀请来的好友和前20名用户的慈善积分。

慈善积分公式：用户自己的第一笔捐赠+好友的所有捐赠+好友人数*35

数据库表结构

	qw_zoetis_donate	报名/捐赠信息
字段名	中文名	字段类型	备注说明
id	表主键	integer	自动增长
user_id	用户ID	integer
donate_price	直接捐赠金额	decimal
enroll_price	报名金额	decimal
total_price	总捐赠金额	decimal	等于donate_price+enroll_price
donate_unit	捐赠机构ID	integer
city_id	报名城市ID	integer
pay_status	支付状态	string
pay_time	支付时间	integer
order_no	捐赠订单编号	string
entry_no	报名编号	string
created_time	创建时间	integer
updated_time	更新时间	integer
inviter_id	邀请用户ID	integer
is_active	是否累计到自己的总捐赠金额	boolean	每个用户只有第一笔捐赠值为true
	qw_zoetis_user	用户捐赠金额
字段名	中文名	字段类型	备注说明
id	活动ID	integer	自动增长
user_id	活动期间用户能够参与的次数限制	string
donate_unit			用户is_active=1那条记录的donate_unit
total_price			自己is_active=ture和邀请来好友所用有捐赠金额之合
created_time	创建时间	integer
updated_time	更新时间	integer

实例分析

因为要在慈善机构排行榜上看到自己、自己邀请来的用户和前20名用户的慈善积分。因为：1，以上这些条件的查询结果结构相同；2，为避免查询当前用户和邀请好友的慈善积分和名次时出现N+1次查询。所以我们使用嵌套语句一次查询出满足所有条件的记录。

查询语句

假如我们已经先查出当前用户和好友的ID是(7, 29087, 29089, 29097)

SELECT *
FROM (
	SELECT user_id, total_price, invited_user_count, user_score,
	@current_score AS before_score,
	@row_no:=@row_no+1 AS row_no, 
	CASE WHEN @current_score <> user_score THEN @score_no:=@row_no ELSE @score_no:=@score_no END AS user_score_no
	, @current_score := user_score AS after_score
	FROM (
		SELECT qw_zoetis_user.user_id, qw_zoetis_user.total_price,
		qw_zoetis_user.updated_time, 
		COUNT(DISTINCT qw_zoetis_donate.user_id) AS invited_user_count, 
		IF(COUNT(DISTINCT qw_zoetis_donate.user_id) IS NOT NULL, COUNT(DISTINCT qw_zoetis_donate.user_id)*35, 0)+qw_zoetis_user.total_price AS user_score
		FROM qw_zoetis_user
		LEFT JOIN qw_zoetis_donate ON qw_zoetis_donate.inviter_id=qw_zoetis_user.user_id AND qw_zoetis_donate.pay_status='success'
		WHERE qw_zoetis_user.donate_unit>=0
		GROUP BY qw_zoetis_user.user_id
	) t1
	ORDER BY user_score DESC, updated_time ASC
)t2
WHERE row_no<=20 OR user_id IN (7, 29087, 29089, 29097);

查询结果截图

 

素材文件：链接: https://pan.baidu.com/s/1bpEJ82F 密码: ktfs

转载于:https://my.oschina.net/laifuzi/blog/861271