sql - 在 SQL 中给出最小或最大数据库其他列时查找其他列数据

Question

我有一个表格，它准确地指定了每个员工在特定办公室的日期和时间。EmployeeTable看起来像这样:


ID
员工ID
日期P
时间P


1
11111
1397/01/02
01:30

2
11111
1398/05/09
05:30

3
11111
1398/06/07
05:10

4
22222
1398/08/09
06:12

5
22222
1399/02/01
07:15

6
11111
1399/07/02
08:51

7
11111
1399/08/06
12:20

8
33333
1399/09/04
20:01

9
33333
1399/12/08
22:05

10
33333
1400/01/01
23:11

11
33333
1400/02/05
14:10

12
22222
1400/04/05
16:25


我想为每个在办公室的员工准确选择最小和最大日期和时间:


ID
员工ID
最小日期
时间最小日期P
最大日期P
时间最大日期P


1
11111
1397/01/02
01:30
1398/06/07
05:10

2
22222
1398/08/09
06:12
1399/02/01
07:15

3
11111
1399/07/02
08:51
1399/08/06
12:20

4
33333
1399/09/04
20:01
1400/02/05
14:10

5
22222
1400/04/05
16:25
1400/04/05
16:25


我的 SQL 代码是:

with tab1 as 
(
    select * 
    from EmployeeTable
), tab2 as 
(
    select 
        t1.*,
        case when lag(t1.EmployeeID) over(order by t1.id) is null then 1
            when lag(t1.EmployeeID) over(order by t1.id) = t1.EmployeeID then 0
            else 1
       end lg
  from tab1 t1
)
, tab3 as (
select t1.*,
       sum(t1.lg) over(order by t1.id) grp
  from tab2 t1
)
select t1.EmployeeID,
       min(t1.DateP) as min,
       TimeP,
       max(t1.DateP)as max,
       TimeP
  from tab3 t1
 group by t1.EmployeeID, t1.grp

但是上面的代码有错误。
每个人都可以帮助我吗？

Answer 1

这是一个缺口和孤岛问题。解决此问题的一种方法是使用行号差异方法:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY DateP, TimeP) rn1,
              ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY DateP, TimeP) rn2
    FROM EmployeeTable
),
cte2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY EmployeeID, rn1 - rn2
                                 ORDER BY DateP, TimeP) rn_first,
              ROW_NUMBER() OVER (PARTITION BY EmployeeID, rn1 - rn2
                                 ORDER BY DateP DESC, TimeP DESC) rn_last
    FROM cte
)

SELECT
    EmployeeID,
    MAX(CASE WHEN rn_first = 1 THEN DateP END) AS MinDateP,
    MAX(CASE WHEN rn_first = 1 THEN TimeP END) AS TimeMinDateP,
    MAX(CASE WHEN rn_last = 1  THEN DateP END) AS MaxDateP,
    MAX(CASE WHEN rn_last = 1  THEN TimeP END ) AS TimeMaxDateP
FROM cte2
GROUP BY
    EmployeeID,
    rn1 - rn2
ORDER BY
    MIN(DateP),
    MIN(TimeP);

请注意，如果您使用单个日期时间列来表示日期和时间，则第二个 CTE 中的逻辑将完全没有必要。像您目前所做的那样将日期和时间分开通常是没有好处的。