本文介绍了我怎样才能对选定的行进行排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是我的查询:
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)
这篇关于我怎样才能对选定的行进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！