一个简单问题，怎样写SQL最简单
部门(部门ID)
员工(部门ID, 员工ID)
工资(员工ID, 年, 月, 工资)
给定某年某月, 如果返回一个列表, 某年某月每个部门中工资最高的员工(返回部门ID, 员工ID, 工资)
[解决办法]
select
a.*,b.年,b.月,b.工资
from
员工 a,工资 b
where
a.员工ID=b.员工ID
and
b.年=yyyy and b.月=mm
and
not exists(select
1
from
员工 c,工资 d
where
c.部门ID=d.部门ID
and
d.年=b.年 and d.月=b.月 and d.工资> b.工资)
[解决办法]
select b.部门ID,a.员工ID,min(工资)
from 工资 a , 员工 b
where a.员工ID=b.员工ID and a.年=指定年 and a.月 = 指定月
group by b.部门ID,a.员工ID
[解决办法]
--try
select *
from
(
select 部门ID, 工资=max(工资)
from 员工 A
left join 工资 B on A.员工ID=B.员工ID
where B.年= ' ' and B.月= ' '
group by A.部门ID
)tmp
left join 工资 on tmp.工资=工资.工资
left join 员工 on tmp.部门ID=员工.部门ID
where B.年= ' ' and B.月= ' '
[解决办法]
select Y.ID,Y.id,G.g from Y,G where G.g=(select max(g) from G) and G.y=yy and G.m=mm
group by Y.id
[解决办法]
select d.darid,e.empid,e.salary from
(select a.darid,max(b.salary) as salary from department a ,employee b where a.empid =b.empid group by a.darid) as t ,
department d , employee e
where t.darid = d.darid and e.salary = t.salary and d.empid = e.empid
[解决办法]
更喜欢3楼的
select a.bmid,b.ygid,a.gz
from (select bb.bmid,max(aa.gz) as gz from gz aa join yg bb on aa.ygid=bb.ygid group by bb.bmid
where nian= ' ' and yue = ' '
) a
join gz b on a.gz=b.gz
我的异常网推荐解决方案：软件开发者薪资,http://www.myexception.cn/other/1391128.html
