有方案选择给定时间内跟踪表中存在的唯一值
表值,例如
HistoryId EmpId RoleName TimeStamp
1 1 Developer 8:00:00 AM
2 1 Lead 8:00:00 PM
3 1 Mgr 5:00:00 PM
4 2 Lead 8:00:00 AM
5 2 Developer2 5:00:00 PM
6 2 Mgr2 8:00:00 PM
7 3 Mgr 8:00:00 AM
8 3 Lead2 5:00:00 PM
9 3 Developer3 8:00:00 PM
11 3 Developer4 8:30:00 PM
12 1 lead5 8:15:00 PM
想要在给定时间内为用户选择唯一的RoleName值,
示例-如果我将时间设为6PM,则应选择6PM时每个用户的最新状态
我尝试如下选择,但是给定了所有值,因为我只需要为通过的时间值提供最新值
Select EmpId, RoleName, MAX(TimeStamp)
From [dbo].[EmpRoleHistory]
Where TimeStamp <= '2020-02-06 18:00:00.000'
GROUP BY EmpId, RoleName
ORDER BY EmpId
预期结果:
1 Mgr 2020-02-06 17:00:00.000
2 Developer2 2020-02-06 17:00:00.000
3 Lead2 2020-02-06 17:00:00.000
当前结果:
1 Developer 2020-02-06 08:00:00.000
1 Mgr 2020-02-06 17:00:00.000
2 Developer2 2020-02-06 17:00:00.000
2 Lead 2020-02-06 08:00:00.000
3 Lead2 2020-02-06 17:00:00.000
3 Mgr 2020-02-06 08:00:00.000
问题在这里
GROUP BY EmpId, RoleName
如我所见,相同之处与EmpId
有所不同RoleName
。
这就是为什么您获得不同价值的原因。
;with cte_TempTable as
(
Select EmpId, MAX(TimeStamp) as TimeStamp
From [dbo].[EmpRoleHistory]
Where TimeStamp <= '2020-02-06 17:00:00.000'
GROUP BY EmpId
)
select e.*
from EmpRoleHistory e
inner join cte_TempTable c on e.EmpId = c.EmpId
where e.TimeStamp = c.TimeStamp
ORDER BY e.EmpId
输出量
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句