• 中文
    • English
  • 注册
  • 赞助本站

    • 支付宝
    • 微信
    • QQ

    感谢一直支持本站的所有人!

    • 查看作者
    • 4-10:order by语句对查询结果排序

      对查询结果进行排序的语法结构:

      [ORDER BY {col_name | expr | position}[ASC | DESC],...]

      ASC:升序(默认),DESC:降序,比如我们按照id降序排序:

      mysql> SELECT * FROM users ORDER BY id DESC;
      +----+----------+----------------------------------+-----+------+
      | id | username | password                         | age | sex  |
      +----+----------+----------------------------------+-----+------+
      |  9 | 111      | 222                              |  33 | NULL |
      |  8 | BEn      | 456                              |  17 |    0 |
      |  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
      |  5 | John     | 123                              |  10 |    0 |
      |  4 | John     | 123                              |  35 |    0 |
      |  3 | Tom      | 123                              |  27 |    0 |
      |  2 | John     | 123                              |  38 |    0 |
      |  1 | Tom      | 123                              |  27 |    0 |
      +----+----------+----------------------------------+-----+------+
      8 rows in set (0.00 sec)

      如果对多个字段进行排序时,如果第一个字段可以排出想要的结果,则忽略其他字段,如果不能,则遵守第二个字段,依次类推。

      首先按照年龄默认排序:

      mysql> SELECT * FROM users ORDER BY age;
      +----+----------+----------------------------------+-----+------+
      | id | username | password                         | age | sex  |
      +----+----------+----------------------------------+-----+------+
      |  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
      |  5 | John     | 123                              |  10 |    0 |
      |  8 | BEn      | 456                              |  17 |    0 |
      |  1 | Tom      | 123                              |  27 |    0 |
      |  3 | Tom      | 123                              |  27 |    0 |
      |  9 | 111      | 222                              |  33 | NULL |
      |  4 | John     | 123                              |  35 |    0 |
      |  2 | John     | 123                              |  38 |    0 |
      +----+----------+----------------------------------+-----+------+
      8 rows in set (0.00 sec)

      可以看到有同龄的用户,我们可以设置如果同龄,则按照id降序排列:

      mysql> SELECT * FROM users ORDER BY age,id DESC;
      +----+----------+----------------------------------+-----+------+
      | id | username | password                         | age | sex  |
      +----+----------+----------------------------------+-----+------+
      |  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
      |  5 | John     | 123                              |  10 |    0 |
      |  8 | BEn      | 456                              |  17 |    0 |
      |  3 | Tom      | 123                              |  27 |    0 |
      |  1 | Tom      | 123                              |  27 |    0 |
      |  9 | 111      | 222                              |  33 | NULL |
      |  4 | John     | 123                              |  35 |    0 |
      |  2 | John     | 123                              |  38 |    0 |
      +----+----------+----------------------------------+-----+------+
      8 rows in set (0.00 sec)

    • 0
    • 0
    • 0
    • 1.8k
    • 请登录之后再进行评论

      登录
    • 做任务
    • 实时动态
    • 偏好设置
    • 返回顶部
    • 单栏布局 侧栏位置: