日期:2014-05-16  浏览次数:20853 次

mysql 单表多条件查询结果排序问题,在线等,万分感谢,急!!!
我有一张表,表里有多个字段,其中两字段:状态t_stu(数字1-10),发布时间:t_tim,现在查询结果想先t_stu=1排最前面,然后其余的全部按时间先后逆序(不按t_stu大小排序了)。我用order by t_stu,t_tim desc得到结果不对,是t_stu=1排在前面了,然后后面是在t_stu的基础上按时间逆序,这不是我想要的结果。

比如有一组数据:
  t_stu t_tim
  5 09:35
  3 09:28
  1 09:38
  5 09:12
  4 09:48
  3 09:22
按上查询出来是:
  t_stu t_tim
  1 09:38
  3 09:28
  3 09:22
  4 09:48
  5 09:35
  5 09:12
我想要的结果是如下的结果,请问怎么实现,非常感谢
  t_stu t_tim
  1 09:38
  4 09:48 ,也就是除状态以外,其他全部按时间逆序
  5 09:35  
  3 09:28
  3 09:22
  5 09:12

我是初学,还请各位大虾们多多指教,在此感

------解决方案--------------------
SQL code
mysql> select * from tend;
+------+-----+------+------+------------+-------+
| id   | cnt | god  | aid  | t_tim      | t_stu |
+------+-----+------+------+------------+-------+
|  230 |   1 |  422 |  243 | 1263969843 |     6 |
|  301 |   1 | 1630 | 1089 | 1263994231 |     1 |
| 1024 |   1 | 1700 | 1168 | 1264424112 |    10 |
| 1156 |   1 |    0 |    0 | 1264503945 |     5 |
| 1241 |   1 |  800 |  536 | 1264562344 |     1 |
| 1487 |   1 |  471 |  313 | 1264755894 |     9 |
| 1497 |   1 |  607 |  446 | 1264757909 |     1 |
| 1521 |   2 |  384 |  229 | 1264765911 |     8 |
| 1527 |   1 |  384 |  229 | 1264767574 |     6 |
| 1541 |   1 |  518 |  337 | 1264770033 |     3 |
| 1798 |   1 |  359 |  175 | 1265256419 |     1 |
| 1849 |   1 |  359 |  175 | 1265620065 |     6 |
| 1882 |   1 |  471 |  313 | 1266670635 |     7 |
| 1883 |   1 |  432 |  265 | 1266670796 |     4 |
| 1912 |   1 | 1266 |  826 | 1266756302 |     3 |
| 1956 |   1 |  516 |  325 | 1266831684 |     6 |
| 1971 |   1 |    0 |    0 | 1266903628 |     1 |
+------+-----+------+------+------------+-------+
17 rows in set (0.00 sec)

mysql> select *
    -> from tend
    -> order by t_stu=1 desc,t_tim desc;
+------+-----+------+------+------------+-------+
| id   | cnt | god  | aid  | t_tim      | t_stu |
+------+-----+------+------+------------+-------+
| 1971 |   1 |    0 |    0 | 1266903628 |     1 |
| 1798 |   1 |  359 |  175 | 1265256419 |     1 |
| 1497 |   1 |  607 |  446 | 1264757909 |     1 |
| 1241 |   1 |  800 |  536 | 1264562344 |     1 |
|  301 |   1 | 1630 | 1089 | 1263994231 |     1 |
| 1956 |   1 |  516 |  325 | 1266831684 |     6 |
| 1912 |   1 | 1266 |  826 | 1266756302 |     3 |
| 1883 |   1 |  432 |  265 | 1266670796 |     4 |
| 1882 |   1 |  471 |  313 | 1266670635 |     7 |
| 1849 |   1 |  359 |  175 | 1265620065 |     6 |
| 1541 |   1 |  518 |  337 | 1264770033 |     3 |
| 1527 |   1 |  384 |  229 | 1264767574 |     6 |
| 1521 |   2 |  384 |  229 | 1264765911 |     8 |
| 1487 |   1 |  471 |  313 | 1264755894 |     9 |
| 1156 |   1 |    0 |    0 | 1264503945 |     5 |
| 1024 |   1 | 1700 | 1168 | 1264424112 |    10 |
|  230 |   1 |  422 |  243 | 1263969843 |     6 |
+------+-----+------+------+------------+-------+
17 rows in set (0.06 sec)

mysql>