weixin_38369037
weixin_38369037
2018-11-28 03:24

msql union 行号被重置

  • select

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
  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

0条回答

为你推荐

换一换