根据避免两个不同表的部分,有没有办法将其组合到一个查询中以获得结果?在历史表中,相对于货币‘USD’有USD多行,我只需要移动到历史记录中的最后一个值就可以挑选出来
Select M.CR CR, M.Rate RATE, M.Operator OPERATOR
From Rate M
Where M.CR = 'USD'
Select M.CR CR, M.Rate RATE, M.Operator OPERATOR
, max(M.FROMSOURCEDATE) As MovedDate
From RateHistory M
Where M.CR = 'USD'
Group by M.CR, M.Rate, M.Operator
这里只有FROMSOURCEDATE
是我唯一可以用日期更新的字段,当它移动到历史表中时.
当前结果是
CR | RATE | OPERATOR |
---|---|---|
USD | 3.65 | * |
CR | RATE | OPERATOR | MOVEDDATE |
---|---|---|---|
USD | 3.75 | * | 2023-10-29 22:57:22.820 |
USD | 3.73 | * | 2023-10-29 22:56:22.810 |
USD | 3.67 | * | 2023-10-29 22:56:21.820 |
USD | 3.77 | * | 2023-10-29 22:57:22.803 |
USD | 3.55 | * | 2023-9-29 21:57:22.820 |
USD | 3.45 | * | 2023-9-29 21:56:22.810 |
USD | 3.71 | * | 2023-8-29 20:55:25.505 |
USD | 3.75 | * | 2023-8-29 20:55:25.410 |
USD | 3.66 | * | 2023-7-29 22:57:22.820 |
USD | 3.68 | * | 2023-7-29 22:57:22.820 |
USD | 3.75 | * | 2023-7-29 22:57:22.820 |
所需结果如下所示
CR | NEWRATE | OLDRATE | OPERATOR |
---|---|---|---|
USD | 3.65 | 3.75 | * |