RDER BY NAME, SALARY ASC;
-- 将结果按 NAME 降序排序:
SELECT * FROM COMPANY ORDER BY NAME DESC;
9. Group By 分组
在 SELECT 语句中,GROUP BY 子句放在 WHERE 子句之后,放在 ORDER BY 子句之前。
-- 了解每个客户的工资总额,则可使用 GROUP BY 查询
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
让我们使用下面的 INSERT 语句在 COMPANY 表中另外创建三个记录:
INSERT INTO COMPANY VALUES (8, ‘Paul’, 24, ‘Houston’, 20000.00 );
INSERT INTO COMPANY VALUES (9, ‘James’, 44, ‘Norway’, 5000.00 );
INSERT INTO COMPANY VALUES (10, ‘James’, 45, ‘Texas’, 5000.00 );
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Paul |
32 |
California |
20000.0 |
2 |
Allen |
25 |
Texas |
15000.0 |
3 |
Teddy |
23 |
Norway |
20000.0 |
4 |
Mark |
25 |
Rich-Mond |
65000.0 |
5 |
David |
27 |
Texas |
85000.0 |
6 |
Kim |
22 |
South-Hall |
45000.0 |
7 |
James |
24 |
Houston |
10000.0 |
8 |
Paul |
24 |
Houston |
20000.0 |
9 |
James |
44 |
Norway |
5000.0 |
10 |
James |
45 |
Texas |
5000.0 |
-- 用同样的 GROUP BY 语句来对所有记录按 NAME 列进行分组:
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
NAME |
SUM(SALARY) |
Allen |
15000 |
David |
85000 |
James |
20000 |
Kim |
45000 |
Mark |
65000 |
Paul |
40000 |
Teddy |
20000 |
10. Having 分组设置条件限制
HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。
WHERE 子句在所选列上设置条件,而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Paul |
32 |
California |
20000.0 |
2 |
Allen |
25 |
Texas |
15000.0 |
3 |
Teddy |
23 |
Norway |
20000.0 |
4 |
Mark |
25 |
Rich-Mond |
65000.0 |
5 |
David |
27 |
Texas |
85000.0 |
6 |
Kim |
22 |
South-Hall |
45000.0 |
7 |
James |
24 |
Houston |
10000.0 |
8 |
Paul |
24 |
Houston |
20000.0 |
9 |
James |
44 |
Norway |
5000.0 |
10 |
James |
45 |
Texas |
5000.0 |
-- 将显示名称计数大于 2 的所有记录:
SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;
ID |
NAME |
AGE |
ADDRESS |
SALARY |
10 |
James |
45 |
Texas |
5000 |
11. Distinct 消除重复
-- 没有任何重复的条目:
SELECT DISTINCT name FROM COMPANY;
NAME |
Paul |
Allen |
Teddy |
Mark |
David |
Kim |
James |