22,209
社区成员
发帖
与我相关
我的任务
分享
WITH D AS (
-- 先选出到8月份为止的调整数据(假定调整时间为8月份的工资在9月起效)
SELECT * FROM B WHERE 调整时间 < '2014-08-01'
UNION ALL
SELECT * FROM C WHERE 调整时间 < '2014-08-01'
),
E AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY 员工code ORDER BY 调整时间 DESC) n
FROM D
),
F AS (
-- 选出离8月份最近的调整数据
SELECT 员工code,金额,调整时间
FROM E
WHERE n = 1
)
SELECT A.企业code,
A.员工code,
F.金额 AS '工资'
FROM A
JOIN F
ON F.员工code = A.员工code
WHERE ...
select* from (
select *,ROW_NUMBER()over(partition by 员工code order by 调整时间 desc)as n
from C join A on a.员工code =c.code and a.企业CODE='a1'
and c.调整时间<'2014-09-01') as t
where t.n=1
逻辑就是找出该员工调整时间小于‘2014-09-01’的最大日期。就是8月份的生效工资。select * from A LEFT JOIN C ON A.员工Code=C.员工Code WHERE 调整时间>='2014-08-01' AND 调整时间<'2014-09-01'
你的意思是这个吗?