mysql 高级按查询排序 [英] mysql advanced order by query

问题描述

我在 MySQL 中有一个这样的表:

I have a table like this in MySQL:

+-----+------+
| id  | type |
+-----+------+
| 149 |    8 |
| 150 |    7 |
| 151 |    8 |
| 152 |    7 |
| 153 |    5 |
| 154 |    6 |
| 155 |    3 |
| 156 |    2 |
| 157 |    4 |
| 158 |    2 |
| 159 |    1 |
| 160 |    0 |
+-----+------+

我想对这个表格进行排序并得到如下结果:

I would like to sort this table and receive results like this:

+-----+------+
| id  | type |
+-----+------+
| 151 |    8 |
| 152 |    7 |
| 154 |    6 |
| 153 |    5 |
| 157 |    4 |
| 155 |    3 |
| 158 |    2 |
| 159 |    1 |
| 160 |    0 |
| 149 |    8 |
| 150 |    7 |
| 156 |    2 |
+-----+------+

作为进一步的解释，我想对类型列进行排序，就像这样继续倒计时:8,7,6,5,4,3,2,1,0,8,7,6,5,4,3,2,1,0,8,7,...

As further explanation, I want to sort type column like continues count down like this : 8,7,6,5,4,3,2,1,0,8,7,6,5,4,3,2,1,0,8,7,...

可以这样对表格进行排序吗?或者通过程序或其他方式达到那个结果?

Is it possible to sort table like that? or achieve that result by procedures or something else?

推荐答案

 SELECT id, TYPE FROM (
   SELECT id, TYPE, 
   IF(@myvar = 0 OR @myvar = TYPE, @counter := @counter + 1, @counter := 1) sequence,  
   @myvar := TYPE FROM mytable 
   JOIN (SELECT @myvar := 0, @counter := 0 ) a
   ORDER BY TYPE DESC, id) b 
ORDER BY sequence, TYPE DESC,  id

此查询适用于任何级别.

This query will work for any level.

如有必要，请放置外部查询以仅获取相关字段.

Put an outer query if necessary to fetch only relevant fields.

检查这个 SqlFiddle

这篇关于mysql 高级按查询排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！