ORDER對查詢結果進行排序

怪怪怪怪怪發表於2020-09-25

降序、升序與隨機排序

ASC 是升序，DESC是降序，預設是升序排列，注意，這裡是對查詢結果進行排序
按照年齡的升序, 降序排列

mysql> SELECT*FROM cms_user ORDER BY id ASC;
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
|  1 | 張三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male    |
|  2 | 張三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   23 | female  |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   46 | male    |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   54 | male    |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male    |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
12 rows in set (0.00 sec)

mysql> SELECT*FROM cms_user ORDER BY id DESC;
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male    |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   54 | male    |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   46 | male    |
|  2 | 張三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   23 | female  |
|  1 | 張三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male    |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
12 rows in set (0.00 sec)

使用欄位的位置按照降序排列
這裡我用了order by 7 意思就是按照proId的升序排列的

mysql> SELECT*FROM cms_user ORDER BY 7;
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
|  1 | 張三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male    |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
|  2 | 張三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   23 | female  |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   54 | male    |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   46 | male    |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male    |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+

按照多個欄位排

按照年齡升序，id降序來排列
會先考慮第一個欄位的排序，再在第一個欄位相同的情況下按照第二個欄位來排序

mysql> SELECT*FROM cms_user ORDER BY proId, age DESC;
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
|  1 | 張三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male    |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   54 | male    |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
|  2 | 張三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   23 | female  |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   46 | male    |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male    |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
12 rows in set (0.00 sec)

可以通過ORDER BY RAND()進行隨機排序

mysql> SELECT*FROM cms_user ORDER BY RAND();
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex     |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   27 | female  |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 |   60 | unknown |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   64 | female  |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   28 | male    |
|  2 | 張三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   23 | female  |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   46 | male    |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   27 | male    |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   31 | unknown |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   98 | female  |
|  1 | 張三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 | NULL | male    |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   54 | male    |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   77 | female  |
+----+-----------+--------------+-------------+------------+----------+-------+------+---------+
12 rows in set (0.01 sec)

總結

1. SELECT

SELECT 欄位名稱 FROM tbl_name
運用AS 給欄位或是table起別名

2. WHERE

新增比較倆條件例如>=, <=, !=,
新增指定範圍， BETWEEN AND
新增出現的集合 IN
模糊查詢 LIKE
多條件查詢 AND， OR

3. GROUP BY

GROUP BY 指的是分組
按欄位分組
按欄位位置分組
聚合函式 GROUP_CONCAT()
– 顯示某個欄位分組下的詳情
COUNT（）
– 查詢每個分組中的總人數
– 欄位有空值時，不統計空值
MAX(）
MIN()
AVG()
SUM()
ROLLUP
– 對分組後的每一列進行總結

4. HAVING

對分組進行二次篩選

5. ORDER BY

對分組進行升，降，或亂序的排列

mysql> SELECT id,age,sex, GROUP_CONCAT(username) AS username,
    -> COUNT(*) AS totalusers,
    -> SUM(age) AS Age
    -> FROM cms_user
    -> GROUP BY sex
    -> HAVING COUNT(*)>=2
    -> ORDER BY age DESC, id ASC;
+----+------+---------+---------------------------------+------------+------+
| id | age  | sex     | username                        | totalusers | Age  |
+----+------+---------+---------------------------------+------------+------+
|  2 |   23 | female  | 張三丰,long,queen,blek,lily     |          5 |  289 |
|  1 | NULL | male    | 張三,章子怡,ring,king,rose      |          5 |  155 |
| 11 |   31 | unknown | john,test1                      |          2 |   91 |
+----+------+---------+---------------------------------+------------+------+
3 rows in set (0.00 sec)

NC65對單表單據查詢的結果進行排序
2020-09-28
排序
SQL Server對組合查詢結果排序方法
2010-10-14
SQLServer排序
MySQL order by 排序結果不正確
2020-04-01
MySql排序
大資料MongoDB之mgo驅動如何對查詢結果進行排序（正序逆序多欄位排序）？
2019-03-20
大資料MongoDB排序
[Mysql 查詢語句]——對查詢結果進一步的操作
2017-03-18
MySql
使用oracle decode對錶字元列進行order by 排序
2011-12-26
Oracle字元排序
xsl中，對xml文件查詢的結果再次查詢
2007-07-11
XML
備忘：laravel 對查詢結果集可以迴圈where查詢
2021-12-02
Laravel
mysql查詢結果多列拼接查詢
2018-08-17
MySql
mysql按照漢字拼音進行order by排序
2017-12-18
MySql排序
儲存過程結果進行查詢 select 存過過程
2012-06-11
儲存過程
django不使用序列化器來進行查詢結果序列化
2020-10-27
Django
navicat 匯出查詢結果
2013-11-07
mysql匯出查詢結果
2013-04-28
MySql
將Json載入到.NET物件並對結果進行過濾和排序
2019-01-01
JSON物件排序
oracle中對LONG列進行查詢
2013-12-03
Oracle
SQL查詢結果集對注入的影響及利用
2009-10-15
SQL
Mybatis模糊查詢結果為空
2020-11-14
MyBatis
ps aux查詢結果解釋
2022-11-26
UX
對字典進行排序
2018-06-07
排序
[ORACLE BUG]查詢結果錯誤--臨時表並行
2013-02-28
Oracle並行
MySQL查詢結果匯出方式總結
2019-01-26
MySql
Mybatis 查詢語句結果集總結
2018-08-03
MyBatis
【記錄】sqlserver列轉行查詢並將查詢結果用逗號分隔開
2020-12-25
SQLServer
第二章：查詢與排序-------遞迴形式進行插入排序
2019-03-06
排序遞迴
Oracle中實現查詢結果按照in中條件排序 InStr函式
2011-12-03
Oracle排序函式
sqlserver使用order by case when進行優先順序排序
2018-12-11
SQLServer排序
Mysql-基本練習(10-設定分組條件、查詢結果排序、限制查詢結果返回的數量、TRUNCATE刪除表記錄)
2018-05-17
MySql排序
PLSQL Developer 複製查詢結果卡頓
2019-03-04
SQLDeveloper
使用查詢結果更新表的方法
2014-06-19
oracle查詢結果外面新增引號
2012-08-14
Oracle
Oracle查詢結果儲存為XML
2007-06-08
OracleXML
oracle 查詢結果的各種格式
2011-10-21
Oracle
MaxCompute如何對SQL查詢結果實現分頁獲取
2019-04-16
SQL
[鐵柱分享]第五期：如何在 Laravel Eloquent 中對 all() 的結果進行排序？
2021-07-08
Laravel排序
報表表樣多excel表格與sql查詢結果一一匹配_order by decode and 中間表
2013-03-05
ExcelSQL
用於對執行慢的查詢進行優化
2008-12-22
優化
ES查詢之查詢屬性過濾、結果高亮顯示
2024-04-09

ORDER對查詢結果進行排序

降序、升序 與隨機排序

總結

相關文章

降序、升序與隨機排序