mysql - 如何交替排序选定的行

Question

这是我的查询:

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
FROM 
(
  SELECT rid, rootid, which, COUNT(*) AS cnt
  FROM
  (
    SELECT rid, rootid, 'vote' which FROM p_likes
    UNION ALL 
    SELECT rid, rootid, 'comment' which FROM p_comments
    UNION ALL 
    SELECT rid, rootid, 'friend' which FROM relations
  ) 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.cnt desc;

下面是上述查询的输出:(// 表示该记录的值与该问题无关。)

+---------+-----------+----------+------------+-------------+-------+
| user_id | user_name | liker_id | liker_name | which_table | total |
+---------+-----------+----------+------------+-------------+-------+
| //      | //        | //       | //         | vote        | 7     |
| //      | //        | //       | //         | vote        | 5     |
| //      | //        | //       | //         | vote        | 3     |
| //      | //        | //       | //         | comment     | 3     |
| //      | //        | //       | //         | vote        | 2     |
| //      | //        | //       | //         | comment     | 2     |
| //      | //        | //       | //         | comment     | 2     |
| //      | //        | //       | //         | vote        | 1     |
| //      | //        | //       | //         | vote        | 1     |
| //      | //        | //       | //         | vote        | 1     |
| //      | //        | //       | //         | comment     | 1     |
| //      | //        | //       | //         | friend      | 1     |
+---------+-----------+----------+------------+-------------+-------+

我要做的就是交替对行进行排序。如您所见，目前我根据 total 列对结果进行排序。虽然我需要根据 total 和 which_table 对它们进行排序。像这样:(预期输出)

+---------+-----------+----------+------------+-------------+-------+
| user_id | user_name | liker_id | liker_name | which_table | total |
+---------+-----------+----------+------------+-------------+-------+
| //      | //        | //       | //         | vote        | 7     |
| //      | //        | //       | //         | comment     | 3     |
| //      | //        | //       | //         | friend      | 1     |
| //      | //        | //       | //         | vote        | 5     |
| //      | //        | //       | //         | comment     | 2     |
| //      | //        | //       | //         | vote        | 3     |
| //      | //        | //       | //         | comment     | 2     |
| //      | //        | //       | //         | vote        | 2     |
| //      | //        | //       | //         | comment     | 1     |
| //      | //        | //       | //         | vote        | 1     |
| //      | //        | //       | //         | vote        | 1     |
| //      | //        | //       | //         | vote        | 1     |
+---------+-----------+----------+------------+-------------+-------+

我该怎么做？

Answer 1

请尝试以下查询。使用 ROW_NUMBER() OVER(PARTITION BY ..) 为每个 'which' 项目生成排名/记录编号，并根据此进行排序。 (希望这对你有用，我没有表架构或示例数据脚本来尝试自己)

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
FROM 
(
  SELECT rid, rootid, which, COUNT(*) AS cnt
        ,ROW_NUMBER() OVER(PARTITION  BY which ORDER BY rid) AS new_order
  FROM
  (
    SELECT rid, rootid, 'vote' which FROM p_likes
    UNION ALL 
    SELECT rid, rootid, 'comment' which FROM p_comments
    UNION ALL 
    SELECT rid, rootid, 'friend' which FROM relations
  ) 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 new_order,x.cnt desc;