MySQL ORDER BY 排序查詢：ASC/DESC 多欄位排序完整指南

ORDER BY 基礎語法

ORDER BY 關鍵字用於對查詢結果進行排序。

語法

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC;

• ASC：升序 (預設)，由小到大、A 到 Z
• DESC：降序，由大到小、Z 到 A

單欄位排序範例

$result = mysql_query("SELECT * FROM Persons ORDER BY Age");

輸出 (依年齡升序)：

Glenn Quagmire 33
Peter Griffin 35

降序排序

$result = mysql_query("SELECT * FROM Persons ORDER BY Age DESC");

輸出 (依年齡降序)：

Peter Griffin 35
Glenn Quagmire 33

多欄位排序

當第一欄位值相同時，才依第二欄位排序。

語法

SELECT column_name(s) FROM table_name ORDER BY column1, column2;

範例：先依姓氏排序，再依名字排序

SELECT * FROM Persons ORDER BY LastName, FirstName;

搭配 WHERE 條件排序

SELECT * FROM Persons 
WHERE City = 'Taipei' 
ORDER BY Age DESC, LastName ASC;

PHP 完整範例

<?php
$con = mysql_connect("localhost", "peter", "abc123");
if (!$con) die('Could not connect: ' . mysql_error());

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons ORDER BY Age");

echo "<table border='1'>
<tr><th>FirstName</th><th>LastName</th><th>Age</th></tr>";

while ($row = mysql_fetch_array($result)) {
    echo "<tr>";
    echo "<td>" . $row['FirstName'] . "</td>";
    echo "<td>" . $row['LastName'] . "</td>";
    echo "<td>" . $row['Age'] . "</td>";
    echo "</tr>";
}

echo "</table>";
mysql_close($con);
?>

效能提示

• 排序欄位若有索引，效能大幅提升
• 大量資料排序會消耗記憶體，可考慮 LIMIT 限制筆數
• ORDER BY RAND() 隨機排序效能極差，大表慎用

現代寫法：MySQLi/PDO 語法相同，僅連接方式不同。