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
排序
大資料MongoDB之mgo驅動如何對查詢結果進行排序（正序逆序多欄位排序）？
2019-03-20
大資料MongoDB排序
MySQL order by 排序結果不正確
2020-04-01
MySql排序
備忘：laravel 對查詢結果集可以迴圈where查詢
2021-12-02
Laravel
mysql查詢結果多列拼接查詢
2018-08-17
MySql
將Json載入到.NET物件並對結果進行過濾和排序
2019-01-01
JSON物件排序
SpringBoot整合Jpa對資料進行排序、分頁、條件查詢和過濾
2023-05-12
Spring Boot排序
django不使用序列化器來進行查詢結果序列化
2020-10-27
Django
第二章：查詢與排序-------遞迴形式進行插入排序
2019-03-06
排序遞迴
對字典進行排序
2018-06-07
排序
sqlserver使用order by case when進行優先順序排序
2018-12-11
SQLServer排序
147. 對連結串列進行插入排序
2020-11-22
排序
【記錄】sqlserver列轉行查詢並將查詢結果用逗號分隔開
2020-12-25
SQLServer
Python對字典進行排序
2018-07-22
Python排序
MaxCompute如何對SQL查詢結果實現分頁獲取
2019-04-16
SQL
[20190306]奇怪的查詢結果.txt
2019-03-06
Mybatis模糊查詢結果為空
2020-11-14
MyBatis
ps aux查詢結果解釋
2022-11-26
UX
MySQL查詢結果匯出方式總結
2019-01-26
MySql
Mybatis 查詢語句結果集總結
2018-08-03
MyBatis
LeetCode-147-對連結串列進行插入排序
2022-01-30
LeetCode排序
Mysql-基本練習(10-設定分組條件、查詢結果排序、限制查詢結果返回的數量、TRUNCATE刪除表記錄)
2018-05-17
MySql排序
排序和查詢
2019-11-25
排序
JPA SQL 查詢、結果集對映(@NamedNativeQuery、@ColumnResult註解說明)
2018-06-29
SQL
[鐵柱分享]第五期：如何在 Laravel Eloquent 中對 all() 的結果進行排序？
2021-07-08
Laravel排序
[Python] Pandas 對資料進行查詢、替換、篩選、排序、重複值和缺失值處理
2021-02-11
Python排序
力扣 147. 對連結串列進行插入排序
2020-11-30
力扣排序
ES查詢之查詢屬性過濾、結果高亮顯示
2024-04-09
MYSQL order by排序與索引關係總結
2018-04-13
MySql排序索引
python 對字典的值進行排序
2020-04-17
Python排序
PostgreSQL函式：返回表查詢結果集
2018-11-18
SQL函式
PLSQL Developer 複製查詢結果卡頓
2019-03-04
SQLDeveloper
DBeaver 匯出多個查詢結果集
2024-03-06
org.apache.ibatis.exceptions.PersistenceException:記錄mybatis 查詢結果對映異常
2020-12-05
ApacheExceptionMyBatis
第三章：查詢與排序（下）----------- 3.22 總結:10種排序演算法的對比分析
2019-03-11
排序演算法
報表也可以根據單元格計算後結果進行排序
2019-12-18
排序
第二章：查詢與排序---------遞迴、查詢與排序補充
2019-03-05
排序遞迴
C# dataGridView展示資料庫查詢結果
2020-10-11
C#View資料庫

ORDER對查詢結果進行排序

降序、升序 與隨機排序

總結

相關文章

降序、升序與隨機排序