我怎样才能对选定的行进行排序 [英] How can I order selected rows alternatively

问题描述

这是我的查询:

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;

这是上面查询的输出:(//表示该记录的值与此问题无关.)

And here is the output of query above: (// means the value of that record isn't related to this question.)

+---------+-----------+----------+------------+-------------+-------+
| 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对其进行排序.像这样:(预期输出)

All I'm trying to do is sorting rows alternatively. As you see, currently I order the results based on total column. While I need to sort them both based on total and which_table alternatively. Something like this: (expected output)

+---------+-----------+----------+------------+-------------+-------+
| 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     |
+---------+-----------+----------+------------+-------------+-------+

我该怎么做?

推荐答案

我认为这并不那么困难-我的以下模型在原理上与问题没有什么不同，并且我使用相关子查询来分配投票(在我的模型中为实况)所有ID的总计

I don't think this is all that difficult - my model below doesn't differ in principal from the question and I use correlated sub queries to allocate the vote (in my model live) total for all whichs for an id

MariaDB [sandbox]> select * from onetime_contest;
+------+-----------+------------+
| id   | status    | valid_till |
+------+-----------+------------+
|    1 | live      | 2017-01-01 |
|    2 | waiting   | 2017-01-01 |
|    3 | completed | 2017-01-01 |
|    4 | Waiting   | 2017-01-01 |
|    5 | live      | 2017-06-01 |
|    6 | waiting   | 2017-06-01 |
|    7 | completed | 2017-06-01 |
|    8 | Waiting   | 2017-06-01 |
|    1 | Live      | 2017-01-01 |
|    2 | Live      | 2017-01-01 |
|    3 | Live      | 2017-01-01 |
|    3 | Waitng    | 2017-01-01 |
|    4 | Live      | 2017-01-01 |
|    6 | Live      | 2017-01-01 |
|    7 | Live      | 2017-01-01 |
|    8 | Live      | 2017-01-01 |
|    1 | Waiting   | 2017-01-01 |
|    2 | Waiting   | 2017-01-01 |
|    2 | Live      | 2017-01-01 |
|    2 | Waiting   | 2017-01-01 |
|    2 | Live      | 2017-01-01 |
|    2 | Live      | 2017-01-01 |
+------+-----------+------------+
22 rows in set (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]>
MariaDB [sandbox]> select * from
    -> (
    -> select id, status ,count(*) as t1 ,count(*) as t2 from onetime_contest o1 where status = 'Live' group by id,status
    -> union
    -> select id, status ,count(*) as t1 ,(select count(*) from onetime_contest o2 where status = 'Live' and o2.id = o1.id  group by o2.id,o2.status)
    ->   from onetime_contest o1 where status = 'Waiting' group by id,status
    -> union
    -> select id, status ,count(*) as t1 ,(select count(*) from onetime_contest o2 where status = 'Live' and o2.id = o1.id  group by o2.id,o2.status)
    ->  from onetime_contest o1 where status = 'Completed' group by id,status
    -> ) s
    -> order by s.t2 desc,s.id,
    -> case when s.status = 'Live' then 1
    ->  when s.status = 'Waiting' then 2
    -> else 3 end;
+------+-----------+----+------+
| id   | status    | t1 | t2   |
+------+-----------+----+------+
|    2 | Live      |  4 |    4 |
|    2 | waiting   |  3 |    4 |
|    1 | live      |  2 |    2 |
|    1 | Waiting   |  1 |    2 |
|    3 | Live      |  1 |    1 |
|    3 | completed |  1 |    1 |
|    4 | Live      |  1 |    1 |
|    4 | Waiting   |  1 |    1 |
|    5 | live      |  1 |    1 |
|    6 | Live      |  1 |    1 |
|    6 | waiting   |  1 |    1 |
|    7 | Live      |  1 |    1 |
|    7 | completed |  1 |    1 |
|    8 | Live      |  1 |    1 |
|    8 | Waiting   |  1 |    1 |
+------+-----------+----+------+
15 rows in set (0.00 sec)

这篇关于我怎样才能对选定的行进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！