有两个表 一个是
表：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