选择上次薪资

Question

我有两个表(我的例子)：

TB1

Emp_code     salary     year   month 
-------      -----      ----   -----
1             2000       2015   1
1             2000       2015   2 
2             4000       2015   2
2             4000       2015   3

TB2

code          name       Age 
-------      -----      ----   
1             MMMM       32
1             MMMM       32 
2             KKKK       25   
2             KKKK       25   

我需要选择最近(月份和年份)获取工资>>

结果应如以下table>>所示

code          name       Age    salary   year   month
-------      -----      ----    ------   ----   -----
1             MMMM       32      2000    2015     2
2             KKKK       25      4000    2015     3

Answer 1

为employee和salary表创建脚本

CREATE TABLE [dbo].[Employee](
[Empcode] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[Age] [varchar](50) NULL) ON [PRIMARY]


CREATE TABLE [dbo].[Salary](
[Empcode] [varchar](50) NULL,
[Salary] [varchar](50) NULL,
[Year] [varchar](50) NULL,
[Month] [varchar](50) NULL
) ON [PRIMARY]

薪资表数据

1   10000   2017    1
1   10000   2017    2
2   40000   2017    2
2   40000   2017    3

employee表数据

1   aaa 25
2   bbb 25

查询:-

select e.empcode,e.name,e.age,s.salary,max(s.year) as year,max(s.month) as month from salary s join employee e on s.empcode=e.empcode group by e.empcode,e.name,e.age,s.salary

输出:-

1   aaa 25  10000   2017    2
2   bbb 25  40000   2017    3

Answer 2

SELECT *
FROM ( 
       SELECT t1.code,
              t2.name,
              t2.Age,
              t1.salary,
              t1.year,
              t1.month,
              ROW_NUMBER OVER(PARTITION BY t1.Emp_code ORDER BY t1.year desc,t1.month desc) AS RN 
       FROM TB1 t1
       INNER JOIN TB2 t2 ON t1.Emp_code=t2.code
                                                 ) X
WHERE X.RN=1

Answer 3

使用窗口函数和distinct

select . . .
from (select tb1.*,
             row_number() over (partition by emp_code order by year desc, month desc) as seqnum
      from tb1
     ) tb1 join
     (select distinct tb2.*
      from tb2
     ) tb2
     on tb1.code = tb2.emp_code;