22,300
社区成员




--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([FacilityID] int,[BeginDate] Date,[WorkerNumber] int)
Insert #T
select 1,'2019-07-03',25 union all
select 1,'2019-07-05',25 union all
select 1,'2019-07-06',25 union all
select 1,'2019-07-07',24 union all
select 1,'2019-07-09',24 union all
select 1,'2019-07-10',24 union all
select 1,'2019-07-12',23 union all
select 1,'2019-07-13',23 union all
select 1,'2019-07-15',23 union all
select 1,'2019-07-16',24 union all
select 1,'2019-07-17',24 union all
select 1,'2019-07-18',24
Go
--测试数据结束
SELECT t1.FacilityID,t1.BeginDate,t1.WorkerNumber FROM (
SELECT *,ROW_NUMBER()OVER(PARTITION BY t.WorkerNumber,rn ORDER BY t.BeginDate) rn1 FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY WorkerNumber ORDER BY FacilityID, BeginDate)
- ROW_NUMBER() OVER (ORDER BY FacilityID, BeginDate) rn
FROM #T)t)t1 WHERE t1.rn1=1 ORDER BY t1.BeginDate
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T(FACILITYID INT,BEGINDATE DATE,WORKERNUMBER INT)
INSERT INTO #T
SELECT 1,'2019-07-01',25 UNION ALL
SELECT 1,'2019-07-02',24 UNION ALL
SELECT 1,'2019-07-05',24 UNION ALL
SELECT 1,'2019-07-06',24 UNION ALL
SELECT 1,'2019-07-12',24 UNION ALL
SELECT 1,'2019-07-15',24 UNION ALL
SELECT 1,'2019-07-16',24 UNION ALL
SELECT 1,'2019-07-20',23 UNION ALL
SELECT 1,'2019-07-22',23 UNION ALL
SELECT 1,'2019-07-25',23 UNION ALL
SELECT 1,'2019-08-02',24 UNION ALL
SELECT 1,'2019-07-18',23
GO
WITH CTE
AS
(SELECT *,ROW_NUMBER() OVER (ORDER BY FACILITYID,BEGINDATE) AS SEQ FROM #T)
SELECT * FROM CTE A
WHERE SEQ=1 OR (EXISTS (SELECT 1 FROM CTE WHERE SEQ=A.SEQ-1 AND A.WORKERNUMBER<>WORKERNUMBER))
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([FacilityID] int,[BeginDate] Date,[WorkerNumber] int)
Insert #T
select 1,'2019-07-03',25 union all
select 1,'2019-07-05',25 union all
select 1,'2019-07-06',25 union all
select 1,'2019-07-07',24 union all
select 1,'2019-07-09',24 union all
select 1,'2019-07-10',24 union all
select 1,'2019-07-12',23 union all
select 1,'2019-07-13',23 union all
select 1,'2019-07-15',23 union all
select 1,'2019-07-16',24 union all
select 1,'2019-07-17',24 union all
select 1,'2019-07-18',24
Go
--测试数据结束
SELECT t1.FacilityID,t1.BeginDate,t1.WorkerNumber FROM (
SELECT * ,ROW_NUMBER()OVER(PARTITION BY t.mindate ORDER BY t.BeginDate) rn FROM (
Select *,DATEADD(DAY,-ROW_NUMBER()OVER(ORDER BY FacilityID)+1,BeginDate) AS mindate from #T
)t)t1 WHERE rn=1
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([FacilityID] int,[BeginDate] Date,[WorkerNumber] int)
Insert #T
select 1,'2019-07-05',25 union all
select 1,'2019-07-06',25 union all
select 1,'2019-07-08',24 union all
select 1,'2019-07-09',24 union all
select 1,'2019-07-10',24 union all
select 1,'2019-07-22',23 union all
select 1,'2019-07-23',23 union all
select 1,'2019-07-24',23
Go
--测试数据结束
SELECT t1.FacilityID,t1.BeginDate,t1.WorkerNumber FROM (
SELECT * ,ROW_NUMBER()OVER(PARTITION BY t.mindate ORDER BY t.BeginDate) rn FROM (
Select *,DATEADD(DAY,-ROW_NUMBER()OVER(ORDER BY FacilityID)+1,BeginDate) AS mindate from #T
)t)t1 WHERE rn=1