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 DualUnion All select 1,2,10 From dualUnion All select 1,3,10 From dualUnion All select 2,1,10 From dualUnion All select 2,2,10 From dualUnion All select 3,1,10 From dualUnion All select 3,2,10 From dualUnion All select 3,3,10 From dual),t2 As(Select 1 eid,'张三'ename From dualUnion All Select 2,'李四' From dualUnion 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, t2where rn = 1And t1.eid = t2.eid
[解决办法]
SQL code
SELECT a.id,a.name,b.month,b.salaryFROM employee A,salary bWHERE 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