前端之家

  1. 首页
  2. 问答

在 SQL

• 问答

我有一个表格，它准确地指定了每个员工在特定办公室的日期和时间。

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 MinDateP TimeMinDateP MaxDateP TimeMaxDateP
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

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

zjs0555 回答：在 SQL

这是一个间隙和孤岛问题。解决此问题的一种方法是使用行数差异方法：

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,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 中的逻辑。像您目前所做的那样将日期和时间分开通常是没有好处的。

sqlsql-servertsql
本文链接：https://www.f2er.com/3061.html

大家都在问