日期:2014-05-17  浏览次数:20849 次

SQL 面试题 (帮解决)
有两个表 一个是
表:employee

表:salary


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


------解决方案--------------------
select a.*,b.* from emplyee a 
inner join salary b on b.employee_id = a.employee
where max(b.month)= b.month
------解决方案--------------------
SQL code
select *
  from (
        select t.*,
               row_number() over(partition by employee_id order by month desc) rn
          from salary t
       )
 where rn = 1

------解决方案--------------------
select sum(salary) from salary
group by month
having month = 3
------解决方案--------------------
SQL code
SELECT * FROM SALARY WHERE (EMPLOYEE_ID,MONTH) IN
(SELECT EMPLOYEE_ID,MAX(MONTH) FROM SALARY GROUP BY EMPLOYEE_ID)

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


------解决方案--------------------
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 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

------解决方案--------------------
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 )

------解决方案--------------------
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