多表查询-2

--查询出武汉地区所有的员工信息 要求显示部门名称以及员工的详细资料
select PeopleId 员工编号,DepartmentName 部门名称,PeopleName 员工姓名,
PeopleSex 员工性别,PeopleBirth 生日,PeopleSalary 月薪,PeoplePhone 电话,PeopleAddress 地址
from People
left join Department on People.DepartmentId = Department.DepartmentId
--查询出武汉地区所有的员工信息，要求显示部门名称，职级名称以及员工的详细资料
select
PeopleId 员工编号,People.DepartmentId 部门编号,DepartmentName 部门名称,RankName 职级名称,People.RankId 职级编号,PeopleName 员工姓名,
PeopleSex 员工性别,PeopleBirth 生日,PeopleSalary 月薪,PeoplePhone 电话,PeopleAddress 地址
from People
left join Department on People.DepartmentId = Department.DepartmentId
left join [Rank] on People.RankId= [Rank].RankId
where PeopleAddress = '武汉'

--根据部门分组统计员工人数 员工工资总和 平均工资 最高工资和最低工资
select DepartmentName 部门名称,count(*) 员工人数,sum(PeopleSalary) 工资总和,avg(PeopleSalary) 平均工资,
max(PeopleSalary) 最高工资,min(PeopleSalary)最低工资
from People
inner join Department on People.DepartmentId = Department.DepartmentId
group by Department.DepartmentId,DepartmentName

--平均工资在10000以下的不参与统计 并且根据平均工资降序排列
select DepartmentName 部门名称,count(*) 员工人数,sum(PeopleSalary)工资总和,avg(PeopleSalary)平均工资,
max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
from People
inner join Department on People.DepartmentId = Department.DepartmentId
group by Department.DepartmentId,DepartmentName
having avg(PeopleSalary)>=10000
order by avg(PeopleSalary)desc

----根据部门名称 然后根据职位名称
--分组统计员工人数 员工工资总和 平均工资 最高工资和最低工资
select DepartmentName 部门名称,RankName 职级名称,count(*) 员工人数,sum(PeopleSalary) 工资总和,avg(PeopleSalary)平均工资,
max(PeopleSalary) 最高工资,min(PeopleSalary) 最低工资
from People
inner join Department on People.DepartmentId = Department.DepartmentId
inner join [Rank] on People.RankId = [Rank].RankId
group by Department.DepartmentId,DepartmentName,[Rank].RankId,RankName

--自连接 自己连自己
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

select A.DeptId 部门编号,A.DeptName 部门名称,B.DeptName 上级部门 from Dept A
inner join Dept B on A.ParentId = b.DeptId