关键词：最大年龄、员工信息、部门、存储过程
假如，存在如下的员工表(employee)结构：
|
ID |
DeptID |
Age |
|
1 |
1 |
34 |
|
2 |
2 |
24 |
|
3 |
3 |
52 |
|
4 |
3 |
42 |
|
… |
一般用法：
理论指导：写一个SQL语句，找出每个部门年龄最大的员工。
1、显示1号部门最大年龄的员工信息
Select top 1 * from (Select * from employee where(DeptID=1) order by Age) as employee
2、显示2号部门最大年龄的员工信息
Select top 1 * from (select * from employee where(DeptID=2) order by Age) as employee
3、显示n号部门最大年龄的员工信息
Select top 1 * from (select * from employee where(DeptID=n) order by Age) as employee
4、显示每个部门年龄最大的员工信息
Select top 1 * from (Select * from employee where(DeptID=1) order by Age) as employee
union
Select top 1 * from (Select * from employee where(DeptID=2) order by Age) as employee
union
Select top 1 * from (Select * from employee where(DeptID=3) order by Age) as employee
union
…
5、显示每个部门年龄最大的员工信息
Select * from employee where Age=(select max(Age) from employee) and DeptID=1
Union
Select * from employee where Age=(select max(Age) from employee) and DeptID=2
Union
Select * from employee where Age=(select max(Age) from employee) and DeptID=3
…
创建获取部门员工信息的存储过程
Create procedure employeeInfo
@bmh int
As
Select * from employee where DeptID=@bmh
Go
执行存储过程，获取指定部门的员工信息
Exec employeeInfo @bmh=1
知识扩展：
理论指导：写一个SQL语句，找出每个部门年龄最大的员工。
1、显示每个部门年龄最大的部门信息和年龄
select DeptID,max(Age) as Age from employee group by DeptID
2、查询年龄最大的员工的员工编号
Select ID from employee as a where (DeptID,Age) in
(select DeptID,max(Age) as Age from employee group by DeptID)
3、 显示每个部门年龄最大的员工信息和年龄
Select * from employee
Where ID in
(Select ID from employee as a where (DeptID,Age) in
(select DeptID,max(Age) as Age from employee group by DeptID))