记录

/ 后端 / 没有评论 / 648浏览

4月邀请活动

SELECT
	(
		@rowNum := @rowNum + 1 
	) AS 排名 ,x.telephone as 手机号,x.invitationCount as 邀请人数,x.lastInvTimeStr as 最后邀请时间
FROM
	(
	SELECT
		mem.telephone,
		IFNULL( mem.headImage, mem.wxHeadImage ) AS headImage,
		count( inv.id ) AS invitationCount,
		max( inv.id ) AS lastInvTime,
		max( inv.createTime ) AS lastInvTimeStr 
	FROM
		wf_member_invitation inv
		JOIN wf_member mem ON inv.inviterId = mem.userId
		JOIN (
		SELECT
			vip.userId 
		FROM
			wf_vip_order vip
			JOIN ( SELECT userId FROM wf_vip_order WHERE orderStatus = 2 AND orderType != 7 AND vipEndTime > now() ) x ON vip.userId = x.userId 
		AND vip.orderStatus = 2 
		AND vip.orderType != 7 
		AND vip.vipType IN ( 4, 5 ) 
		GROUP BY
			vip.userId 
		) vaildVip ON inv.inviterId = vaildVip.userId
		LEFT JOIN wf_invitation_rank_hide_member hide ON mem.userId = hide.user_id 
	WHERE
		( inv.createTime BETWEEN '2023-04-01 00:00:00' and '2023-04-30 23:59:59')
			AND hide.user_id IS NULL GROUP BY inv.inviterId ORDER BY invitationCount DESC, lastInvTime ASC LIMIT 30
			) x,(
		SELECT
		@rowNum := 0 
	) y
评论已关闭.