Mysql - 如何通过交替(1,2,3,1,2,3,1,2,3,)行对结果进行排序,这可能吗?
我想按客户端 1、2、3 对结果进行排序,然后再按客户端 1、2、3,依此类推。
有没有办法在不使用 for 循环或进行三个单独查询的情况下执行此操作?不仅如此,我正在处理分页数据,因此它需要返回 x 个结果,但要一致。
有什么想法吗?也许可以分组?
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
I want to order my results by client 1, 2, 3, then again client 1, 2, 3, and so on.
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.
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
使用:
MySQL没有任何排名功能,但幸运的是你可以使用变量。关键是当 client_id 与之前的 client_id 不匹配时重置 @rownum 值 - 子查询中的 ORDER BY 是为了确保客户端按顺序排列。
Use:
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.
为什么不
ORDER BY id
?Why not
ORDER BY id
?GROUP BY
不会有帮助。这是否可能在很大程度上取决于您的数据。基本上,您需要一个复杂的ORDER BY
来根据其他值将某些内容组合在一起。例如,使用您提供的示例数据,您可以使用:
这对您的真实数据不太可能有用,但它给出了您需要采用单独字段的想法,使该数据的某些子集达到相同目的排序(在本例中,所有具有相同 10 秒值的内容)并且对 client_id 进行二次排序。
虽然这在很大程度上取决于最终的公式,但像这样的东西应该是一个相对稳定的排序,因此添加分页,例如via
应该返回一致的结果。
当然,这样做意味着您不会从索引中获得任何好处,因此如果您有很多行,它最终会比使用循环/单独查询执行某些操作要慢。
GROUP BY
is not going to be helpful. Whether this is possible is going to depend a lot on your data. Basically you would need a convolutedORDER BY
that hacked something together based on other values.For instance, using the example data you gave, you could use:
This is unlikely to be useful on your real data but it gives the idea that you would need to take a separate field, make some subsets of that data be equivalent for the purpose of sorting (in this case, everything with the same 10s value) and the have a secondary sort on client_id.
Although it depends a lot on the final formula, something like this should be a relatively stable sort, so adding pagination, such as via
should return consistent results.
Of course, doing this means you get no benefits from having indexes so if you have a lot of rows, it will end up being slower than doing something with loops/separate queries.
除非我看错问题了,这就是你想要的吗?
Unless I read the question wrong is this what you want?
听起来像是需要在客户端完成的事情。
Sounds like something to be done client-side.