SQL 面试题 （帮解决）

[xiaoran139]

有两个表 一个是
表：employee

表：salary


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

[iqlife]

SELECT * FROM SALARY WHERE (EMPLOYEE_ID,MONTH) IN
(SELECT EMPLOYEE_ID,MAX(MONTH) FROM SALARY GROUP BY EMPLOYEE_ID)

[xtxjx]

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

[xiaoran139]

[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

[cyousor]

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

看错了

[小灰狼W]

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 )

[codearts]

还是用分析函数简单明了

[wonderful1935]

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

[oraclemch]

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

[gltyi99]

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

[ojuju10]

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

[心中的彩虹]

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

[qxq321]

我也用是用not exists

[xiaoran139]

[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]

无效的标示符~~~

[xiaoran139]

[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]

[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]

是不是 进错房间了~~~

[清风智语]

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 

[tangren]

--练习
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;

[tiantom]

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

[huminghua]

8楼答案不错哦！嘿嘿！