資料結構
網路
關係型資料庫管理系統
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 語言程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理學
化學
生物學
數學
英語
經濟學
心理學
社會學
服裝設計
法律研究如何在 Laravel 中使用 OrderBy 對多個列進行排序？
ORDERBY 子句用於按升序或降序排列表中的列。預設情況下，它按升序排序列，如果需要按降序排序，則應與子句一起使用DSC。
語法
以下是此語句的語法：
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
假設我們在 MySQL 資料庫中使用以下查詢建立了一個名為 Students 的表：
CREATE TABLE students( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL, email VARCHAR(15) NOT NULL, created_at VARCHAR(27) NOT NULL, updated_at VARCHAR(27) NOT NULL, address VARCHAR(3) NOT NULL );
並使用以下 INSERT 語句填充它：
INSERT INTO students(id,name,email,created_at,updated_at,address,age) VALUES (1,'Siya Khan','siya@gmail.com','2022-05-01T13:45:55.000000Z','2022-05-01T13:45:55.000000Z','Xyz', 20); INSERT INTO students(id,name,email,created_at,updated_at,address,age) VALUES (2,'Rehan Khan','rehan@gmail.com','2022-05-01T13:49:50.000000Z','2022-05-01T13:49:50.000000Z','Xyz', 18); INSERT INTO students(id,name,email,address,age) VALUES (3,'Rehan Khan','rehan@gmail.com','testing',20); INSERT INTO students(id,name,email,address,age) VALUES (4,'Rehan','rehan@gmail.com','abcd',15); INSERT INTO students(id,name,email,address,age) VALUES (5,'Nidhi Agarwal','nidhi@gmail.com','abcd',20); INSERT INTO students(id,name,email,address,age) VALUES (6,'Ashvik Khanna','ashvik@gmail.com','oooo',16); INSERT INTO students(id,name,email,address,age) VALUES (7,'Viraj Desai','viraj@gmail.com','test',18); INSERT INTO students(id,name,email,address,age) VALUES (8,'Priya Singh','priya@gmail.com','test123',20);
如果檢索建立的表，它將如下所示：
+----+---------------+------------------+-----------------------------+-----------------------------+---------+------+ | id | name | email | created_at | updated_at |address | age | +----+---------------+------------------+-----------------------------+-----------------------------+---------+------+ | 1 | Siya Khan | siya@gmail.com | 2022-05-01T13:45:55.000000Z | 2022-05-01T13:45:55.000000Z | Xyz | 20 | | 2 | Rehan Khan | rehan@gmail.com | 2022-05-01T13:49:50.000000Z | 2022-05-01T13:49:50.000000Z | Xyz | 18 | | 3 | Rehan Khan | rehan@gmail.com | NULL | NULL | testing | 20 | | 4 | Rehan | rehan@gmail.com | NULL | NULL | abcd | 15 | | 5 | Nidhi Agarwal | nidhi@gmail.com | NULL | NULL | abcd | 20 | | 6 | Ashvik Khanna | ashvik@gmail.com | NULL | NULL | oooo | 16 | | 7 | Viraj Desai | viraj@gmail.com | NULL | NULL | test | 18 | | 8 | Priya Singh | priya@gmail.com | NULL | NULL | test123 | 20 | +----+---------------+------------------+-----------------------------+-----------------------------+---------+------+ 8 rows in set (0.00 sec)
在本文中，我們將使用 eloquent 模型 student，因此要使用 orderBy，語法將是：
Student::orderBy();
以下是呼叫多個列上的 order by 的 MySQL 查詢：
SELECT * FROM 'students' ORDER BY fieldname ASC/DESC
示例 1
以下程式使用 ORDERBY 子句檢索表的多個列：
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Models\Student; class StudentController extends Controller { public function index() { echo $student = Student::orderBy('name', 'DESC') ->orderBy('email', 'ASC') ->get(); } }
輸出
以上程式的輸出為：
[{"id":1,"name":"Siya Khan","email":"siya@gmail.com","address":"Xyz"},
{"id":2,"name":"Rehan Khan","email":"rehan@gmail.com","address":"Xyz"}]
使用的查詢
以上程式的 select 查詢為：
SELECT * FROM 'students' ORDER BY 'name' DESC, 'email' ASC
如果在 MySQL 中執行以上查詢，您將獲得以下輸出
mysql> SELECT * FROM students ORDER BY 'name' DESC, 'email' ASC; +----+------------+-----------------+-----------------------------+-----------------------------+---------+ | id | name | email | created_at | updated_at | address | +----+------------+-----------------+-----------------------------+-----------------------------+---------+ | 1 | Siya Khan | siya@gmail.com | 2022-05-01T13:45:55.000000Z | 2022-05-01T13:45:55.000000Z | Xyz | | 2 | Rehan Khan | rehan@gmail.com | 2022-05-01T13:49:50.000000Z | 2022-05-01T13:49:50.000000Z | Xyz | +----+------------+-----------------+-----------------------------+-----------------------------+---------+ 2 rows in set (0.00 sec)
示例 2
以下是如何在 Laravel 中使用 ORDERBY 子句的另一個示例。這裡，在 name 和 email 上給出的orderBy為ASC：
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Models\Student; class StudentController extends Controller { public function index() { echo $student = Student::orderBy('name', 'ASC') ->orderBy('email', 'ASC') ->get(); } }
輸出
以上程式的輸出為：
[{"id":2,"name":"Rehan Khan","email":"rehan@gmail.com","created_at":"2022-05-01T13:49:50.000000Z","updated_at":"2022-05-01T13:49:50.000000Z","address":"Xyz"},{"id":1,"name":"Siya Khan","email":"siya@gmail.com","created_at":"2022-05-01T13:45:55.000000Z","updated_at":"2022-05-01T13:45:55.000000Z","address":"Xyz"}]
使用的查詢
以上情況下的查詢為：
SELECT * FROM 'students' ORDER BY 'name' ASC, 'email' ASC;
在 MySQL 中執行時，輸出如下
mysql> SELECT * FROM students ORDER BY name ASC, email ASC; +----+------------+-----------------+-----------------------------+-----------------------------+---------+ | id | name | email | created_at | updated_at | address | +----+------------+-----------------+-----------------------------+-----------------------------+---------+ | 2 | Rehan Khan | rehan@gmail.com | 2022-05-01T13:49:50.000000Z | 2022-05-01T13:49:50.000000Z | Xyz | | 1 | Siya Khan | siya@gmail.com | 2022-05-01T13:45:55.000000Z | 2022-05-01T13:45:55.000000Z | Xyz | +----+------------+-----------------+-----------------------------+-----------------------------+---------+ 2 rows in set (0.00 sec)
更新於: 2022-08-29
6K+ 瀏覽量
廣告