Mysql-我如何通过交替(1,2,3,1,2,3,1,2,3,)行来排序结果,有可能吗? [英] Mysql - How do I order results by alternating (1,2,3, 1, 2, 3, 1, 2, 3,) rows, is it possible?
问题描述
我想按客户端1、2、3,然后再按客户端1、2、3等对我的结果进行排序.
I want to order my results by client 1, 2, 3, then again client 1, 2, 3, and so on.
有没有一种方法,而无需使用for循环或进行三个单独的查询?不仅如此,而且我正在处理分页数据,因此它需要返回x结果,但是要始终如一.
Is there a way to do this without using a for loop or making three separate queries? Not only that, but I am working with paginated data so it needs to return x results, but consistently.
有什么想法吗?也许是GROUP BY?
Any ideas? GROUP BY maybe?
client_id project_id project_name
---------- ---------- ------------
1 42 project abc
2 49 project xyz
3 41 project 123
1 22 project apple
2 29 project orange
3 21 project banana
推荐答案
使用:
SELECT x.client_id,
x.project_id,
x.project_name
FROM (SELECT t.client_id,
t.project_id,
t.project_name,
CASE
WHEN @client_id != t.client_id THEN @rownum := 0
WHEN @client_id = t.client_id THEN @rownum := @rownum + 1
ELSE @rownum
END AS rank,
@client_id := t.client_id
FROM TABLE t,
(SELECT @rownum := 0, @client_id
ORDER BY t.client_id) r) x
ORDER BY x.rank, x.client_id
MySQL没有任何排名功能,但是幸运的是您可以使用变量.关键是在client_id与先前的client_id不匹配时重置@rownum值-子查询中的ORDER BY是为了确保客户端顺序正确.
MySQL doesn't have any ranking functionality, but luckily you can use variables. The key was resetting the @rownum value when the client_id doesn't match the previous client_id - the ORDER BY in the subquery is to ensure that clients are in order.
这篇关于Mysql-我如何通过交替(1,2,3,1,2,3,1,2,3,)行来排序结果,有可能吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!