R
group by分組查詢裡，以聚合函數做為條件篩選，要使用having，不能用where.- 語法結構：
select–from–where–group by–having–order by–limit 運行順序：
from–where–group by–having–select–order by–limit- 自連接：自己連自己(父節點&子節點的概念)
如果想要實作這樣的效果1 2 3 4 5 6 7 8
/*------------------------ 部門編號 部門名稱 上級部門 3 軟體研發部 軟體部 4 硬體測試部 硬體部 ---------------------------*/ select A.DeptId 部門編號, A.DeptName 部門名稱, B.DeptName 上級部門 from Dept A --子表 inner join Dept B on A.ParentId = B.DeptId --父表
範例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
--(1)查詢出台北地區所有的員工資料，要求顯示部門名稱以及員工的詳細資料(顯示中文別名)
select EmployeeID 員工編號, DepartmentName 部門, EmployeeName 員工姓名, EmployeeSex 性別, EmployeeBirth 員工生日, EmployeeSalary 月薪, EmployeePhone 電話, EmployeeAddress 地址
from Employee
left join Department on Employee.EmployeeID = Department.DepartmentId
where EmployeeAddress = '台北'
--(2)查詢出台北地區所有的員工資料，要求顯示部門名稱、職級、以及員工的詳細資料(顯示中文別名)
select EmployeeID 員工編號, DepartmentName 部門, RankName 職級, EmployeeName 員工姓名, EmployeeSex 性別, EmployeeBirth 員工生日, EmployeeSalary 月薪, EmployeePhone 電話, EmployeeAddress 地址
from Employee
left join Department on Employee.DepartmentId = Department.DepartmentId
left join [Rank] on Employee.RankId = [Rank].RankId
where EmployeeAddress = '台北'
--(3)根據部門分組統計員工人數、員工工資總和、平均工資，最高工資、最低工資
select DepartmentName 部門, count(*)員工人數, sum(EmployeeSalary)工資總和, avg(EmployeeSalary) 平均工資, max(EmployeeSalary) 最高工資, min(EmployeeSalary) 最低工資
from Employee
left join Department on Employee.DepartmentId = Department.DepartmentId
group by Department.DepartmentId, DepartmentName
--(4)根據部門分組統計員工人數、員工工資總和、平均工資，最高工資、最低工資
--平均工資在50000以下的不參與統計，並且根據平均工資降序排列。
select DepartmentName 部門, count(*)員工人數, sum(EmployeeSalary)工資總和, avg(EmployeeSalary) 平均工資, max(EmployeeSalary) 最高工資, min(EmployeeSalary) 最低工資
from Employee
left join Department on Employee.DepartmentId = Department.DepartmentId
group by Department.DepartmentId, DepartmentName
having avg(EmployeeSalary) >= 50000
order by avg(EmployeeSalary) desc
--(5)根據部門名稱，然後根據職位名稱
--分組統計員工人數、員工工資總和、平均工資，最高工資、最低工資
select DepartmentName 部門,RankName 職位名稱,
count(*)員工人數, sum(EmployeeSalary)工資總和, avg(EmployeeSalary) 平均工資, max(EmployeeSalary) 最高工資, min(EmployeeSalary) 最低工資
from Employee --第一張表
left join Department on Employee.DepartmentId = Department.DepartmentId --第二張表
left join [Rank] on Employee.RankId = [Rank].RankId --第三張表
group by Department.DepartmentId, DepartmentName, [Rank].RankId, RankName
group by分組查詢裡，以聚合函數做為條件篩選，要使用having，不能用where.
自連接(自己連自己)
父節點/子節點
1
2
3
4
5
DeptId DeptName ParentId
1 軟體部 0
2 硬體部 0
3 軟體研發部 1
4 硬體測試部 2
前置作業(假資料)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
--自連接
create table Dept
(
DeptId int primary key, --部門編號
DeptName varchar(50), --部門名稱
ParentId int --上級部門編號
)
--一級
insert into Dept(DeptId,DeptName,ParentId)
values(1,'軟體部',0)
insert into Dept(DeptId,DeptName,ParentId)
values(2,'硬體部',0)
--二級
insert into Dept(DeptId,DeptName,ParentId)
values(3,'軟體研發部',1)
insert into Dept(DeptId,DeptName,ParentId)
values(4,'軟體測試部',1)
insert into Dept(DeptId,DeptName,ParentId)
values(5,'軟體實施部',1)
insert into Dept(DeptId,DeptName,ParentId)
values(6,'硬體研發部',2)
insert into Dept(DeptId,DeptName,ParentId)
values(7,'硬體測試部',2)
insert into Dept(DeptId,DeptName,ParentId)
values(8,'硬體實施部',2)
select * from Dept
實作效果
如果我想要實踐這樣的效果格式，就要用自連接
1
2
3
部門編號 部門名稱 上級部門
3 軟體研發部 軟體部
4 硬體測試部 硬體部
實作自連接
同一張表，查詢的時候變成兩張表
1
2
3
4
5
6
7
8
/*
部門編號 部門名稱 上級部門
3 軟體研發部 軟體部
4 硬體測試部 硬體部
*/
select A.DeptId 部門編號, A.DeptName 部門名稱, B.DeptName 上級部門
from Dept A --子表
inner join Dept B on A.ParentId = B.DeptId --父表