思路:
1、计算查询 需求数量
2、截取字符串长度
参照:https://www.cnblogs.com/heyonggang/p/8117754.html
3、排序先后顺序
4、参加活动日期最早排序-为最后规则
5、计算所在排名位置:rank
代码
SELECT
a.*, (@rank :=@rank + 1) AS rank
FROM
(
SELECT
u.id AS u_id,
LEFT(u.nick_name,1),
-- u.phone_number,
u.avatar_url,
(
SELECT
COUNT(vS.id) AS vS
FROM
cem_mp_user uS
INNER JOIN cem_mp_user_is_invited vS ON uS.id = vS.invited_user_id
WHERE
vS.user_id = u.id
) AS user_invited_number,
(
SELECT
COUNT(vS.id) AS vS
FROM
cem_mp_user uS
INNER JOIN cem_mp_user_is_invited vS ON uS.id = vS.invited_user_id
WHERE
vS.user_id = u.id
AND is_vip NOT IN (3, 5)
) AS zy_invited_number,
(
IF (
! (
P.reward_time IS NULL
OR P.reward_time = ''
),
(
SELECT
SUM(`value`)
FROM
cem_mp_user_prizes up
INNER JOIN cem_mp_invitation_present i ON up.present_id = i.id
WHERE
up.user_id = u.id
),
0
)
) AS worth,
(
IF (
P.reward_time IS NULL
OR P.reward_time = '',
now(),
P.reward_time
)
) AS reward
FROM
cem_mp_user AS u
LEFT JOIN cem_mp_user_prizes p ON u.id = p.user_id
AND u.log_type = 1
GROUP BY
u.id
ORDER BY
zy_invited_number DESC,
user_invited_number DESC,
reward ASC
) AS a,
(SELECT @rank := 0) AS rank
结果