SQL 面试题 (帮解决)

xiaoran139 2010-04-28 02:16:10
有两个表 一个是
表:employee

表:salary


目前 要求 求出 人员 最后一个月的工资信息。
...全文
194 23 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
23 条回复
切换为时间正序
请发表友善的回复…
发表回复
huminghua 2010-04-29
  • 打赏
  • 举报
回复
8楼答案不错哦!嘿嘿!
tiantom 2010-04-29
  • 打赏
  • 举报
回复
select e.id, e.name, 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
tangren 2010-04-28
  • 打赏
  • 举报
回复
--练习
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;
清风智语 2010-04-28
  • 打赏
  • 举报
回复

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

xiaoran139 2010-04-28
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 zhangcunhua 的回复:]

SQL code

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……
[/Quote]

是不是 进错房间了~~~
xiaoran139 2010-04-28
  • 打赏
  • 举报
回复
[Quote=引用 8 楼 wildwave 的回复:]

SQL code
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 ……
[/Quote]

这种思路挺好~~~
xiaoran139 2010-04-28
  • 打赏
  • 举报
回复
[Quote=引用 15 楼 wkc168 的回复:]

SQL code

--少了个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 s……
[/Quote]

无效的标示符~~~
qxq321 2010-04-28
  • 打赏
  • 举报
回复
我也用是用not exists
心中的彩虹 2010-04-28
  • 打赏
  • 举报
回复

--少了个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


心中的彩虹 2010-04-28
  • 打赏
  • 举报
回复

--分析函数较为明白
--多多练习,相互提升
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


ojuju10 2010-04-28
  • 打赏
  • 举报
回复
select * from salary a,employee c
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
gltyi99 2010-04-28
  • 打赏
  • 举报
回复
SELECT * FROM employee;


1 1 张三
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 200

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
);


1 张三 3 400
2 李四 2 100
3 王五 3 200
oraclemch 2010-04-28
  • 打赏
  • 举报
回复
[Quote=引用楼主 xiaoran139 的回复:]
有两个表 一个是
表:employee

表:salary


目前 要求 求出 人员 最后一个月的工资信息。
[/Quote]



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;
wonderful1935 2010-04-28
  • 打赏
  • 举报
回复
Select e.Id, e.Name, Sa.Salary
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) Sa

On e.Id = Sa.Employee_Id
Order By e.Id
codearts 2010-04-28
  • 打赏
  • 举报
回复
还是用分析函数简单明了
小灰狼W 2010-04-28
  • 打赏
  • 举报
回复
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 )
cyousor 2010-04-28
  • 打赏
  • 举报
回复

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



看错了
xiaoran139 2010-04-28
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 xtxjx 的回复:]

select a.*,b.* from emplyee a
inner join salary b on b.employee_id = a.id ---看错字段。
where max(b.month)= b.month
[/Quote]

有问题

此处不允许处理 max(b.month)= b.month
xtxjx 2010-04-28
  • 打赏
  • 举报
回复
select a.*,b.* from emplyee a
inner join salary b on b.employee_id = a.id ---看错字段。
where max(b.month)= b.month

iqlife 2010-04-28
  • 打赏
  • 举报
回复
SELECT * FROM SALARY WHERE (EMPLOYEE_ID,MONTH) IN
(SELECT EMPLOYEE_ID,MAX(MONTH) FROM SALARY GROUP BY EMPLOYEE_ID)
加载更多回复(3)

17,140

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧