select *
from (
select t.*,
row_number() over(partition by employee_id order by month desc) rn
from salary t
)
where rn = 1
SELECT * FROM SALARY WHERE (EMPLOYEE_ID,MONTH) IN
(SELECT EMPLOYEE_ID,MAX(MONTH) FROM SALARY GROUP BY EMPLOYEE_ID)
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
SELECT a.id,a.name,b.month,b.salary
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 )
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;
SELECT * FROM employee;
SELECT * FROM salary;
SELECT
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
);
--分析函数较为明白
--多多练习，相互提升
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;
