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
  }
]

打印页面