1、先创建要用到的例子表,并在表里面插入记录,在sqlserver中做测试
- create table employee(
- id int identity(1,1) primary key ,
- name varchar(50),
- salary bigint,
- deptid int);
- insert into employee values('zs',1000,1),
- insert into employee values('ls',1100,1)
- insert into employee values('ww',1100,1)
- insert into employee values('zl',900,1)
- insert into employee values('zl',1000,2)
- insert into employee values('zl',900,2)
- insert into employee values('zl',1000,2)
- insert into employee values('zl',1100,2)
- select * from employee
2、插入记录后的表结构如下:
3、解这题的思路
(1) 首先要求出各个部门的平均工资。
- select deptid,avg(salary) avgsal from employee group by deptid
结果如下图
(2)这时我们就要用连合查询,即:select * from ta,tb where ta.name=tb.name,这种形式进行查询,我们把employee表与上面(1)查询出来的结果进行连合查询,找出工资大于平均工资的记录。
- select ta.* from employee ta,
- (select deptid,avg(salary) avgsal from employee group by deptid)tb
- where ta.deptid=tb.deptid and ta.salary>tb.avgsal
结果如下图:
(3)列出工资大于平均工资的员工与部门号。并按部门排序,把上面的(2)代码改成
- select ta.deptid,count(*) from employee ta,
- (select deptid,avg(salary) avgsal from employee group by deptid)tb
- where ta.deptid=tb.deptid and ta.salary>tb.avgsal group by ta.deptid order by ta.deptid
结果如下图: