Node.js - MySQL 排序

在 Node.js 應用程式中，您可能希望按升序或降序從 MySQL 資料庫中檢索資料。在 MySQL 中，ORDER BY 子句按指定的順序對 SELECT 語句返回的結果集進行排序。當使用 Node.js 應用程式從 MySQL 資料庫中檢索資料時，mysql 模組中定義的連線物件的 query() 方法的查詢字串引數應該包含 ORDER BY 子句。在本章中，已經透過示例描述了在 Node.js 應用程式中 SELECT 語句中 ORDER BY 子句的各種用例。

SELECT 語句中 ORDER BY 子句的語法如下：

SELECT select_list FROM table_name 
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

您在 ORDER BY 子句之後指定一個或多個要排序的列。預設排序順序為升序 (ASC)。

ORDER BY column1;

要以降序獲取行，請在列名前使用 DESC。

ORDER BY column1 DESC;

數值欄位的升序獲取從最小到最大值的欄位行。而字串欄位（如 VARCHAR 或 TEXT）的升序導致按字母順序從 a 到 z 排序。類似地，對於 DateTime 欄位，升序是指時間順序，即從早期日期到後期日期。

示例

在以下 Node.js 程式碼中，mysql 模組的 query() 方法按 salary 欄位的升序返回 employee 表中的行。

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});

var qry =`SELECT * FROM employee ORDER BY salary;`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, function (err, results) {
      if (err) throw err;
      console.log(results);
   });

   con.end();
});

輸出

[
  RowDataPacket { id: 1, name: 'Ravi', age: 25, salary: 25000 },
  RowDataPacket { id: 3, name: 'Meena', age: 26, salary: 27000 },
  RowDataPacket { id: 2, name: 'Anil', age: 26, salary: 30000 }
]

作為使用帶有欄位的 DESC 關鍵字的降序示例，將查詢字串更改為以下內容：

var qry =`SELECT * FROM employee ORDER BY name DESC;`;

結果結果集將按 employee 表中名稱的降序排列。

[
  RowDataPacket { id: 1, name: 'Ravi', age: 25, salary: 25000 },
  RowDataPacket { id: 3, name: 'Meena', age: 26, salary: 27000 },
  RowDataPacket { id: 2, name: 'Anil', age: 26, salary: 30000 }
]

多列排序

如前所述，您可以在 ORDER BY 子句之後指定一個或多個列。這首先按第一列對行進行排序。第一列值相同的行按第二列的值排序。實際上，這成為排序中的排序或巢狀排序。

為了說明多列排序，我們將使用 MySQL 8.0 安裝中預安裝的 world 資料庫。world 資料庫包含一個 city 表，其結構如下：

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

使用以下 Node.js 程式碼，我們將獲取按人口升序排序的按地區劃分的城市列表。

示例

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "world"
});

var qry =`select * from city where name like 'D%' and countrycode='IND' order by district, population;`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
  
   con.query(qry, function (err, results) {
      if (err) throw err;
      console.log(results);
   });

   con.end();
});

輸出

[
  RowDataPacket {
    ID: 1276,
    Name: 'Dibrugarh',
    CountryCode: 'IND',
District: 'Assam',
    Population: 120127
  },
  RowDataPacket {
    ID: 1350,
    Name: 'Dehri',
    CountryCode: 'IND',
    District: 'Bihar',
    Population: 94526
  },
  RowDataPacket {
    ID: 1138,
    Name: 'Darbhanga',
    CountryCode: 'IND',
    District: 'Bihar',
    Population: 218391
  },
  RowDataPacket {
    ID: 1206,
    Name: 'Durg',
    CountryCode: 'IND',
    District: 'Chhatisgarh',
    Population: 150645
  },
  RowDataPacket {
    ID: 1351,
    Name: 'Delhi Cantonment',
    CountryCode: 'IND',
    District: 'Delhi',
    Population: 94326
  },
  RowDataPacket {
    ID: 1025,
    Name: 'Delhi',
    CountryCode: 'IND',
    District: 'Delhi',
    Population: 7206704
  },
  RowDataPacket {
    ID: 1203,
    Name: 'Dhanbad',
    CountryCode: 'IND',
    District: 'Jharkhand',
    Population: 151789
  },
  RowDataPacket {
    ID: 1119,
    Name: 'Davangere',
    CountryCode: 'IND',
    District: 'Karnataka',
    Population: 266082
  },
  RowDataPacket {
    ID: 1347,
    Name: 'Damoh',
    CountryCode: 'IND',
    District: 'Madhya Pradesh',
    Population: 95661
  },
  RowDataPacket {
    ID: 1186,
    Name: 'Dewas',
    CountryCode: 'IND',
    District: 'Madhya Pradesh',
    Population: 164364
  },
  RowDataPacket {
    ID: 1113,
    Name: 'Dhule (Dhulia)',
    CountryCode: 'IND',
    District: 'Maharashtra',
    Population: 278317
  },
  RowDataPacket {
    ID: 1167,
    Name: 'Dindigul',
    CountryCode: 'IND',
    District: 'Tamil Nadu',
    Population: 182477
  },
  RowDataPacket {
    ID: 1117,
    Name: 'Dehra Dun',
    CountryCode: 'IND',
    District: 'Uttaranchal',
    Population: 270159
  },
  RowDataPacket {
    ID: 1214,
    Name: 'Dabgram',
    CountryCode: 'IND',
    District: 'West Bengal',
    Population: 147217
  },
  RowDataPacket {
    ID: 1082,
    Name: 'Durgapur',
    CountryCode: 'IND',
    District: 'West Bengal',
    Population: 425836
  }
]

列印頁面