有两个表 一个是
表:employee表:salary
目前 要求 求出 人员 最后一个月的工资信息。
表:employee表:salary
目前 要求 求出 人员 最后一个月的工资信息。
解决方案 »
- 在恢复区内不能进行双重备份
- 为什么游标循环的时候结果会被覆盖
- pro*c编程中 预编译选项MODE=ANSI 与MODE=ORACLE 这些在那里设置?为什么MODE=ORACLE 可以直接用变量
- 如何对日志文件做多工,请教!
- Oracle安装进度到44%的时候出现写入错误,江湖救急!!
- 在线提问,这道题用sql语句该怎么写?
- 急!!!ASP页面调用存储过程时建立连接时的问题!在线等!
- Oracle可以免费使用吗?
- 为什么这几句代码执行不了?
- oracle11g,如何获取建表语句?
- ORACLE中distinct函数后去指定的函数(急)
- (急)求助,CICS程序中,Oracle怎样避免插入重复的记录!!!!
inner join salary b on b.employee_id = a.employee
where max(b.month)= b.month
from (
select t.*,
row_number() over(partition by employee_id order by month desc) rn
from salary t
)
where rn = 1
group by month
having month = 3
(SELECT EMPLOYEE_ID,MAX(MONTH) FROM SALARY GROUP BY EMPLOYEE_ID)
inner join salary b on b.employee_id = a.id ---看错字段。
where max(b.month)= b.month
有问题 此处不允许处理 max(b.month)= b.month
With t As
(Select 1 eid,1 mon,10 sal From Dual
Union All select 1,2,10 From dual
Union All select 1,3,10 From dual
Union All select 2,1,10 From dual
Union All select 2,2,10 From dual
Union All select 3,1,10 From dual
Union All select 3,2,10 From dual
Union All select 3,3,10 From dual
),t2 As(
Select 1 eid,'张三'ename From dual
Union All Select 2,'李四' From dual
Union All Select 2,'王五' From dual
)
select t1.*,t2.ename
from (
select t.*,
row_number() over(partition by eid order by mon desc) rn
From t
) t1, t2
where rn = 1
And t1.eid = t2.eid看错了
FROM employee A,salary b
WHERE A.ID=b.employee_id
AND NOT EXISTS(
SELECT 1 FROM salary
WHERE employee_id=b.employee_id
and month>b.month )
From Employee e
Left Join (Select s.Employee_Id, s.Salary
From Salary s,
(Select Employee_Id, Max(Month) As l_Month
From Salary
Group By Employee_Id) Tmp
Where s.Employee_Id = Tmp.Employee_Id
And s.Month = Tmp.l_Month) SaOn e.Id = Sa.Employee_Id
Order By e.Id
select e.ename, a.month, a.salary
from (
select s.*,
row_number() over(partition by s.employee_id order by s.month desc) rn
From salary s
) a, employee e
where a.rn = 1
And a.employee_id = e.id;
2 2 李四
3 3 王五SELECT * FROM salary;1 1 1 1 200
2 2 1 2 300
3 3 1 3 400
4 4 2 1 400
5 5 2 2 100
6 6 3 1 500
7 7 3 2 700
8 8 3 3 200SELECT
e.f_employeename,
s.f_month,
s.f_salary
FROM employee e,salary s
WHERE e.f_employeeid = s.f_employeeid
AND NOT EXISTS
(
SELECT 'x'
FROM salary sa
WHERE sa.f_employeeid = e.f_employeeid
AND sa.f_month > s.f_month
);1 张三 3 400
2 李四 2 100
3 王五 3 200
where not exists(select 1 from salary b where a.employee_id=b.employee_id
and a.month<b.month) and a.employee_id=c.id
--分析函数较为明白
--多多练习,相互提升
select id,name,month,salary
(select employee_id,month,salary
from (select employee_id,month,salary,row_number() over(partition by employee_id order by month desc) rn
from salary) a
where rn=1) t,employee s
where s.id=a.employee_id
--少了个from
select id,name,month,salary
from (select employee_id,month,salary
from (select employee_id,month,salary,row_number() over(partition by employee_id order by month desc) rn
from salary) a
where rn=1) t,employee s
where s.id=a.employee_id
select e.*,x.SALARY
from (
select EMPLOYEE_ID,"MONTH",SALARY
from(
select EMPLOYEE_ID,max(MONTH) as "MONTH"
from salary
group by EMPLOYEE_ID
)x
join employee e
on e.ID = x.EMPLOYEE_ID
)y
SELECT a.*,b.*
FROM employee a, (SELECT s.*
FROM salary s, (SELECT employee_id, MAX(MONTH) MONTH FROM salary GROUP BY employee_id) t
WHERE s.employee_id = t.employee_id AND s.month = t.month) b
WHERE a.id = b.employee_id;
from (select s.*,
row_number() over(partition by s.employee_id order by s.month desc) rn
from salary s) a,
employee e
where a.rn = 1
and a.employee_id = e.id