sql - 查询以获取日期范围的首次登录和上次注销日期时间和门号

Question

有一个包含员工登录详细信息的员工表，例如。

员工表

员工ID	姓名	登录日期时间	注销日期时间
1	一种	1/1/21 8:15	1/1/21 10:55
1	一种	1/1/21 11:30	1/1/21 13:15
1	一种	1/1/21 15:20	1/1/21 17:15
1	一种	1/1/21 18:15	1/1/21 22:15
2	乙	1/1/21 9:15	1/1/21 10:55
2	乙	1/1/21 11:30	1/1/21 13:15
2	乙	1/1/21 15:20	1/1/21 16:15
3	C	1/1/21 8:15	1/1/21 11:15
3	C	1/1/21 12:15	1/1/21 14:25
3	C	1/1/21 15:35	1/1/21 23:56
1	一种	2/1/21 8:15	2/1/21 10:55
1	一种	2/1/21 11:30	2/1/21 13:15
1	一种	2/1/21 15:20	2/1/21 17:15
1	一种	2/1/21 18:15	2/1/21 22:15
2	乙	2/1/21 9:15	2/1/21 10:55
2	乙	2/1/21 11:30	2/1/21 13:15
2	乙	2/1/21 15:20	2/1/21 16:15
3	C	2/1/21 8:15	2/1/21 11:15
3	C	2/1/21 12:15	2/1/21 14:25
3	C	2/1/21 15:35	2/1/21 23:56

现在我的查询是如何获取每个日期的员工详细信息。

预期结果

员工ID	姓名	首次登录	首次登录门号	上次注销	最后登出门数
1	一种	21 年 1 月 1 日上午 8 点 15 分	D1	2021 年 1 月 1 日 22:15	D1
2	乙	21 年 1 月 1 日上午 9:15	D2	2021 年 1 月 1 日 16:15	D1
3	C	21 年 1 月 1 日上午 8 点 15 分	D2	2021 年 1 月 1 日 23:56	D3
1	一种	2/1/21 上午 8:15	D1	2021 年 2 月 1 日 22:15	D1
2	乙	2/1/21 上午 9:15	D2	2021 年 2 月 1 日 16:15	D1
3	C	2/1/21 上午 8:15	D2	2021 年 2 月 1 日 23:56	D3

标签： sqlsql-serverpivot

Answer 1

您需要每个用户的第一次登录和最后一次注销。实现此目的的一种方法是聚合一次以获得每个员工的这两个日期时间，然后加入行：

select
  e.employee_id,
  e.log_name as name,
  f.login_datetime as first_login_datetime,
  f.door as first_login_door,
  l.logout_datetime as last_logout_datetime,
  l.door as last_logout_door
from
(
  select
    employee_id,
    max(name) as log_name,
    min(login_datetime) as min_login_datetime,
    max(logout_datetime) as max_logout_datetime
  from mytable
  group by employee_id
) e
join mytable f on f.employee_id = e.employee_id
              and f.login_datetime = e.min_login_datetime
join mytable l on l.employee_id = e.employee_id
              and l.logout_datetime = e.max_logout_datetime
order by e.employee_id;

至于表中的名称：似乎存在此列，因为员工的姓名可以更改，并且您想记录员工在登录/注销时的姓名。我不知道您是想要每个员工一个结果行还是每个员工/姓名一个结果行。我假设是前者，只显示在表格中找到的他们的名字之一。这可能是也可能不是您想要的。如果不是，那么相应地调整查询应该不会太难。

更新：您希望将其限制为特定日期范围内的登录，例如 2012 年 1 月。您不想看到每个员工的第一次登录和最后一次注销，而是每个员工和登录日期。WHERE这基本上意味着我们必须通过添加子句和扩展来更改子查询GROUP BY：

select
  e.employee_id,
  e.log_name as name,
  e.login_date,
  f.login_datetime as first_login_datetime,
  f.door as first_login_door,
  l.logout_datetime as last_logout_datetime,
  l.door as last_logout_door
from
(
  select
    employee_id,
    convert(date, login_datetime) as login_date,
    max(name) as log_name,
    min(login_datetime) as min_login_datetime,
    max(logout_datetime) as max_logout_datetime
  from mytable
  where login_datetime >= '20210101' and login_datetime < '20210201'
  group by employee_id, convert(date, login_datetime)
) e
join mytable f on f.employee_id = e.employee_id
              and f.login_datetime = e.min_login_datetime
join mytable l on l.employee_id = e.employee_id
              and l.logout_datetime = e.max_logout_datetime
order by e.login_date, e.employee_id;