Sql server 多表查询

--查询出武汉地区的所有员工信息，要求显示部门名城及员工详细资料
select * from People left join Department on People.DepartmentId=Department.DepartmentId
select PeopleId 员工编号,DepartmentName 部门名称,PeopleName 员工姓名 from  People left join Department on People.DepartmentId=Department.DepartmentId

--查询出西安地区所有员工信息，显示部门名称，职级名称，员工的详细资料
select *
from People
left join Department on People.DepartmentId=Department.DepartmentId
left join Rank on People.RankId=Rank.RankId
where PeopleAddress ='西安'

--注意：查询DepartmentId 需要前面加上表名 因为有两个表中都有DepartmentId
select PeopleId 员工编号,People.DepartmentId 部门编号 ,DepartmentName 部门名称,PeopleName 员工姓名
from People
left join Department on People.DepartmentId=Department.DepartmentId
left join Rank on People.RankId=Rank.RankId
where PeopleAddress='西安'
--根据部门分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资
select Department.DepartmentId 部门编号, count(*) 员工人数,sum(PeopleSalary)员工工资总和,avg(PeopleSalary)平均工资,max(PeopleSalary)最低工资,min(PeopleSalary)最低工资
from People
inner join Department on People.DepartmentId=Department.DepartmentId
group by Department.DepartmentId,DepartmentName

--根据部门分组统计员工人数，员工工资总和，平均工资，最高工资，和最低工资
--平均工资在6000以下的不参与统计，并且根据平均工资降序排序
select Department.DepartmentId 部门编号, 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)>=6000
order by avg(PeopleSalary) desc
--根据部门名称，然后根据职位名称
--分组统计员工人数 ，员工工资总和，平均工资，最高工资，和最低工资
select DepartmentName 部门名称,[Rank].RanktName 职位名称,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,RanktName

--自连接（自己连自己）
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,'硬件研发部',2)
insert into Dept(DeptId,DeptName,ParentId ) values(6,'软件测试部',2)

select * from Dept

--部门编号 部门名称 上级部门
--3			软件研发部 软件部
--4			软件测试部  软件部


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