十三、ORDER BY 和GROUP BY - 《PostgreSQL》

1.ORDER BY
- 1.1语法
- 1.2示例
2.GROUP BY
- 2.1语法
- 2.2示例

1.ORDER BY

ORDER BY 用于对一列或者多列数据进行升序（ASC）或者降序（DESC）排列。

1.1语法

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
--您可以在 ORDER BY 中使用一列或者多列，但是必须保证要排序的列必须存在。
--ASC 表示升序，DESC 表示降序。

1.2示例

对AGE 进行升序排列

runoobdb=# SELECT * FROM company ORDER BY age ASC;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  6 | Kim   |  22 | South-Hall                                         |  45000
  3 | Teddy |  23 | Norway                                             |  20000
  7 | James |  24 | Houston                                            |  10000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  2 | Allen |  25 | Texas                                              |  15000
  5 | David |  27 | Texas                                              |  85000
  1 | Paul  |  32 | California                                         |  20000
(7 行记录)

--将对结果根据 address 字段值和 SALARY 字段值进行升序排序：
--只有单address字段中出现相同的值的时候，才会对salary进行排序
runoobdb=# SELECT * FROM company ORDER BY address,salary ASC;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  1 | Paul  |  32 | California                                         |  20000
  7 | James |  24 | Houston                                            |  10000
  3 | Teddy |  23 | Norway                                             |  20000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  6 | Kim   |  22 | South-Hall                                         |  45000
  2 | Allen |  25 | Texas                                              |  15000
  5 | David |  27 | Texas                                              |  85000
(7 行记录)


--同理，我们查看salary和name字段的值进行升序排列，此时只有salary出现相同值时，才会对age进行排序
runoobdb=# SELECT * FROM company ORDER BY salary,age ASC;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  7 | James |  24 | Houston                                            |  10000
  2 | Allen |  25 | Texas                                              |  15000
  3 | Teddy |  23 | Norway                                             |  20000
  1 | Paul  |  32 | California                                         |  20000
  6 | Kim   |  22 | South-Hall                                         |  45000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  5 | David |  27 | Texas                                              |  85000
(7 行记录)

降序排列：

runoobdb=# SELECT * FROM company ORDER BY salary DESC;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  5 | David |  27 | Texas                                              |  85000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  6 | Kim   |  22 | South-Hall                                         |  45000
  3 | Teddy |  23 | Norway                                             |  20000
  1 | Paul  |  32 | California                                         |  20000
  2 | Allen |  25 | Texas                                              |  15000
  7 | James |  24 | Houston                                            |  10000
(7 行记录)

2.GROUP BY

GROUP BY 语句和 SELECT 语句一起使用，用来对相同的数据进行分组。
GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。

2.1语法

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

--在 GROUP BY 子句中，你可以对一列或者多列进行分组，但是被分组的列必须存在于列清单中。

2.2示例

runoobdb=# SELECT name,age FROM company GROUP BY address;
错误:  字段 "company.name" 必须出现在 GROUP BY 子句中或者在聚合函数中使用
第1行SELECT name,age FROM company GROUP BY address;

runoobdb=# SELECT name,age FROM company GROUP BY name;
错误:  字段 "company.age" 必须出现在 GROUP BY 子句中或者在聚合函数中使用
第1行SELECT name,age FROM company GROUP BY name;

--查看每个级别薪资的人有多少
runoobdb=# SELECT salary,COUNT(*) AS number FROM company GROUP BY salary;
 salary | number
--------+--------
  20000 |      2
  85000 |      1
  10000 |      1
  45000 |      1
  15000 |      1
  65000 |      1
(6 行记录)

--在company中添加三条数据
runoobdb=# INSERT INTO company VALUES(8,'Paul',24,'Houston',20000.00);
INSERT 0 1
runoobdb=# INSERT INTO company VALUES(9,'James',44,'Norway',5000.00);
INSERT 0 1
runoobdb=# INSERT INTO company VALUES(10,'James',45,'Texas',5000.00);
INSERT 0 1

--可以看到目前表中是存在重复的名称的
runoobdb=# SELECT * FROM company;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  1 | Paul  |  32 | California                                         |  20000
  2 | Allen |  25 | Texas                                              |  15000
  3 | Teddy |  23 | Norway                                             |  20000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  5 | David |  27 | Texas                                              |  85000
  6 | Kim   |  22 | South-Hall                                         |  45000
  7 | James |  24 | Houston                                            |  10000
  8 | Paul  |  24 | Houston                                            |  20000
  9 | James |  44 | Norway                                             |   5000
 10 | James |  45 | Texas                                              |   5000
(10 行记录)

--统计客户薪资总和
runoobdb=# SELECT name,SUM(salary) FROM company GROUP BY name;
 name  |  sum
-------+-------
 Teddy | 20000
 David | 85000
 Paul  | 40000  --原数据中有两个Paul，现在被合并到了一起
 Kim   | 45000
 Mark  | 65000
 Allen | 15000
 James | 20000  --原数据中有三个James，现在被合并到了一起
(7 行记录)

--统计客户姓名
runoobdb=# SELECT name,COUNT(*) AS number FROM company GROUP BY name;
 name  | number
-------+--------
 Teddy |      1
 David |      1
 Paul  |      2
 Kim   |      1
 Mark  |      1
 Allen |      1
 James |      3
(7 行记录)

--和ORDER BY 一起使用
--按name排序
runoobdb=# SELECT name,COUNT(*) AS number FROM company GROUP BY name ORDER BY name;
 name  | number
-------+--------
 Allen |      1
 David |      1
 James |      3
 Kim   |      1
 Mark  |      1
 Paul  |      2
 Teddy |      1
(7 行记录)

--按number排序
runoobdb=# SELECT name,COUNT(*) AS number FROM company GROUP BY name ORDER BY number;
 name  | number
-------+--------
 Kim   |      1
 David |      1
 Mark  |      1
 Allen |      1
 Teddy |      1
 Paul  |      2
 James |      3
(7 行记录)