大众计算机学习网

专门提供各种电脑教程

Sql语句之查询员工信息详细实例

Admin | 2009-4-19 21:27:08 | 被阅次数 | 7992

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

  假如,存在如下的员工表(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))