SQL 面试题 （帮解决）解决方案

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