员工工资sql

创建表

create table bm(id int,mc varchar(50))

create table ry(id int,mz varchar(50), gz double ,mcid int)

insert into bm values (1,'it')
insert into bm (id,mc) values (2,'sales')

insert into ry values (1,'Joe',70000,1)
insert into ry values (2,'Henry',80000,2)
insert into ry values (3,'Sam',60000,2);

insert into ry values (4,'Max',90000,1) ;
insert into ry values (5,'Janet',69000,1);
insert into ry values (6,'Randy1',85000,1);
insert into ry values (7,'JACK',85000,1);

一、每个部门前两个员工的工资

select emb1.mz ,emb1.gz,bm1.mc from bm as bm1,ry as emb1 where bm1.id=emb1.mcid
and (select count(DISTINCT gz) from ry as emb2 where emb1.mcid=emb2.mcid and emb2.gz>emb1.gz)<2
ORDER BY mc,gz desc

二、每个部门员工的平均工资

select avg(gz) as ‘平均值’,bm1.mc as '部门'  from ry as ry1,bm as bm1 where  ry1.mcid=bm1.id GROUP BY ry1.mcid

三、工资超过平均工资的员工信息

select ry1.mz as '名字',ry1.gz as '工资' ,ry1.mcid as '部门id',bm1.mc as '部门' from ry as ry1,bm as bm1,
(select avg(gz) as avg_gz,ry2.mcid as avgid from ry as ry2 group by ry2.mcid) as tb1
where  ry1.mcid=tb1.avgid and ry1.gz>tb1.avg_gz and ry1.mcid=bm1.id order BY ry1.mcid desc, ry1.gz desc
四、各个部门的总工资

SELECT bm.mc as '名称' ,sum(gz) as '总工资' from ry ,bm  where ry.mcid=bm.id group by ry.mcid