SELECT
A.activationNum,
A.qbid,
A.uin,
A.cn,
A.rankingsNum,
A.POINT AS point
FROM
(
(
SELECT
activationNum,
qbid,
uin,
cn,
createTime,
(@i :=@i + 1) AS rankingsNum,
POINT
FROM
(
SELECT
COUNT(1) AS activationNum,
t.qbid,
c.uin,
c.cn,
c.CREATE_TIME AS createTime,
p.POINT
FROM
t_activity_ticket t,
cn_bind c,
cb_point_1 p
WHERE
t.uin = c.UIN
AND c.UIN = p.UIN
AND t. STATUS = 1
AND t.qbId = 1
GROUP BY
c.uin,
t.qbid
ORDER BY
p.POINT DESC
-- LIMIT 10
) B,
(SELECT @i := 0) it
)
UNION
(
SELECT
activationNum,
qbid,
uin,
cn,
createTime,
(@i :=@i + 1) AS rankingsNum,
POINT
FROM
(
SELECT
COUNT(1) AS activationNum,
t.qbid,
c.uin,
c.cn,
c.CREATE_TIME AS createTime,
p.POINT
FROM
t_activity_ticket t,
cn_bind c,
cb_point_2 p
WHERE
t.uin = c.UIN
AND c.UIN = p.UIN
AND t. STATUS = 1
AND t.qbId = 2
GROUP BY
c.uin,
t.qbid
ORDER BY
p.POINT DESC
-- LIMIT 10
) B,
(SELECT @i := 0) it
)
UNION
(
SELECT
activationNum,
qbid,
uin,
cn,
createTime,
(@i :=@i + 1) AS rankingsNum,
POINT
FROM
(
SELECT
COUNT(1) AS activationNum,
t.qbid,
c.uin,
c.cn,
c.CREATE_TIME AS createTime,
p.POINT
FROM
t_activity_ticket t,
cn_bind c,
cb_point_3 p
WHERE
t.uin = c.UIN
AND c.UIN = p.UIN
AND t. STATUS = 1
AND t.qbId = 3
GROUP BY
c.uin,
t.qbid
ORDER BY
p.POINT DESC
) B,
(SELECT @i := 0) it
)
) A
期望结果 rankingsNum 1 1 1 2
现在结果 rankingsNum 1 2 3 4