17,140
社区成员




--练习
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;
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
--少了个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 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
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 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 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 )
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 * FROM SALARY WHERE (EMPLOYEE_ID,MONTH) IN
(SELECT EMPLOYEE_ID,MAX(MONTH) FROM SALARY GROUP BY EMPLOYEE_ID)