mysql - 我怎样才能获得前十名呢？

这是我的查询：

SELECT
  pr1.id AS user_id,
  pr1.title AS user_name,
  pr2.id AS liker_id,
  pr2.title AS liker_name,
  x.which AS which_table,
  x.cnt AS total,
  x.rank AS rk
FROM
(
  SELECT rid, rootid, which, COUNT(*) AS cnt, rank
  FROM
  (
    SELECT @rank1 := @rank1 + 3 rank, rid, rootid, 'vote' which
    FROM p_likes, (select @rank1 := -2) q
    UNION ALL
    SELECT @rank2 := @rank2 + 3 rank, rid, rootid, 'comment' which
    FROM p_comments, (select @rank2 := -1) q
    UNION ALL
    SELECT @rank3 := @rank3 + 3 rank, rid, rootid, 'friend' which
    FROM relations, (select @rank3 := 0) q
  ) y
  WHERE y.rootid = 1246 AND y.rootid <> y.rid
  GROUP BY y.rid, y.rootid, y.which
) x
INNER JOIN pagesroot pr1 on x.rootid = pr1.id
INNER JOIN pagesroot pr2 on x.rid = pr2.id
ORDER BY x.rank desc, x.cnt desc, x.which
LIMIT 30;

我的问题是关于订购。我想从每个表中获取10行（如果存在）。有3个表（p_likes，p_comments，relations）。因此，结果最多应为30行。

但是我的问题是：我需要另外下订单。我的意思是第一行应该来自p_likes，第二行应该来自p_comments，第三行应该来自relations，第四行应该来自p_likes，依此类推..

我也想首先根据cnt列对它们进行排序。我的意思是，我想要每个表的前十名。

我怎样才能做到这一点？

最佳答案

您应该在要获取订单时添加数字，并按其顺序排列第一个订单，如下所示

SELECT @rank1 := @rank1 + 3 rank, rid, rootid, 'vote',1 as num which
FROM p_likes, (select @rank1 := -2) q group by y.rid, y.rootid limit 1,10
UNION ALL
SELECT @rank2 := @rank2 + 3 rank, rid, rootid, 'comment',2 as num which
FROM p_comments, (select @rank2 := -1) q y.rid, y.rootid limit 1,10
UNION ALL
SELECT @rank3 := @rank3 + 3 rank, rid, rootid, 'friend',3 as num which
FROM relations, (select @rank3 := 0) q  y.rid, y.rootid limit 1,10

关于mysql - 我怎样才能获得前十名呢？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41972905/