关键词：最大年龄、员工信息、部门、存储过程

　　假如，存在如下的员工表(employee)结构：

　　一般用法：

　　理论指导：写一个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))