如何从表中转义一个或多个连续行，inoutMode 为 1 或 0 [英] How to escape one or more consecutive rows from table, inoutMode either 1 or 0

问题描述

我有一个表

如果需要，您可以添加更多分区(例如按天).

I have a table My table with result like as below:

Id      EmpCode        CheckInCheckOutDate     WorkDate     InOutMode
85223   175 2017-10-27 11:20:39.000 2017-10-27 11:20:39.000 0
85832   175 2017-10-27 14:21:21.000 2017-10-27 14:21:21.000 1
85844   175 2017-10-27 14:36:23.000 2017-10-27 14:36:23.000 0
85863   175 2017-10-27 15:21:27.000 2017-10-27 15:21:27.000 1
85878   175 2017-10-27 16:03:46.000 2017-10-27 16:03:46.000 0
86031   175 2017-10-27 18:14:41.000 2017-10-27 18:14:41.000 1
86038   175 2017-10-27 18:34:45.000 2017-10-27 18:34:45.000 0
**86039 175 27.10.2017 18:34:46.000 27.10.2017 18:34:46.000 1
86142   175 2017-10-27 19:51:15.000 2017-10-27 19:51:15.000 1**
86162   175 2017-10-27 20:25:44.000 2017-10-27 20:25:44.000 0
86170   175 2017-10-27 20:31:58.000 2017-10-27 20:31:58.000 1

and i want to result like below:(after escape the one consecutive row which have same value in "InOutMode" column of consecutive rows either 0 or 1 condition has based on workdate, Example for : id is '86039' and '86142' row we have same value '1' in last column so I want to remove first row and want to have escape only old row.)

85223   175 2017-10-27 11:20:39.000 2017-10-27 11:20:39.000 0
85832   175 2017-10-27 14:21:21.000 2017-10-27 14:21:21.000 1
85844   175 2017-10-27 14:36:23.000 2017-10-27 14:36:23.000 0
85863   175 2017-10-27 15:21:27.000 2017-10-27 15:21:27.000 1
85878   175 2017-10-27 16:03:46.000 2017-10-27 16:03:46.000 0
86031   175 2017-10-27 18:14:41.000 2017-10-27 18:14:41.000 1
86038   175 2017-10-27 18:34:45.000 2017-10-27 18:34:45.000 0
86142   175 2017-10-27 19:51:15.000 2017-10-27 19:51:15.000 1
86162   175 2017-10-27 20:25:44.000 2017-10-27 20:25:44.000 0
86170   175 2017-10-27 20:31:58.000 2017-10-27 20:31:58.000 1

解决方案

You could use windowed functions:

WITH cte AS (
  select * ,SUM(CASE WHEN InOutMode=1 THEN 0 ELSE 1 END) -- SUM(1-InOutMode)
            OVER(PARTITION BY EmpCode ORDER BY ID) s
  from test1
), cte2 AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY EmpCode, s ORDER BY id) as rn
  FROM cte 
), cte3 AS (
   SELECT *, MAX(rn) OVER(PARTITION BY EmpCode, s) AS m
   FROM cte2
)
DELETE FROM cte3
WHERE rn > 1 AND rn <> m;

SELECT *
FROM test1;

Rextester Demo

Intermediate result to illustrate idea (flip-flop):

If you need you could add futher partitions (for example by day).

这篇关于如何从表中转义一个或多个连续行，inoutMode 为 1 或 0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！