SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[history](
	[paragraph] [varchar](10) NOT NULL,
	[plc_value] [varchar](50) NULL,
	[update_at] [datetime] NULL);
	
INSERT INTO [history]
VALUES 
-- 第一次
('open', '53', '2021-12-01 01:00:00'),
('open', '53', '2021-12-01 02:00:00'),
('open', '53', '2021-12-01 03:00:00'),
('standby', '53', '2021-12-01 04:00:00'),
('standby', '53', '2021-12-01 05:00:00'),
('down', '53', '2021-12-01 06:00:00'),
('down', '53', '2021-12-01 07:00:00'),
('down', '53', '2021-12-01 08:00:00'),
-- 第二次
('open', '53', '2021-12-02 11:00:00'),
('open', '53', '2021-12-02 12:00:00'),
('open', '53', '2021-12-02 13:00:00'),
('standby', '53', '2021-12-02 14:00:00'),
('standby', '53', '2021-12-02 15:00:00'),
('down', '53', '2021-12-02 16:00:00'),
('down', '53', '2021-12-02 17:00:00'),
('down', '53', '2021-12-02 18:00:00'),
-- 第三次
('open', '53', '2021-12-02 19:00:00'),
('open', '53', '2021-12-02 20:00:00'),
('standby', '53', '2021-12-02 20:10:00'),
('standby', '53', '2021-12-02 21:00:00'),
('down', '53', '2021-12-02 21:30:00'),
('down', '53', '2021-12-02 22:00:00');

SELECT * 
FROM [history]


paragraph
plc_value
update_at


open
53
2021-12-01 01:00:00.000

open
53
2021-12-01 02:00:00.000

open
53
2021-12-01 03:00:00.000

standby
53
2021-12-01 04:00:00.000

standby
53
2021-12-01 05:00:00.000

down
53
2021-12-01 06:00:00.000

down
53
2021-12-01 07:00:00.000

down
53
2021-12-01 08:00:00.000

open
53
2021-12-02 11:00:00.000

open
53
2021-12-02 12:00:00.000

open
53
2021-12-02 13:00:00.000

standby
53
2021-12-02 14:00:00.000

standby
53
2021-12-02 15:00:00.000

down
53
2021-12-02 16:00:00.000

down
53
2021-12-02 17:00:00.000

down
53
2021-12-02 18:00:00.000

open
53
2021-12-02 19:00:00.000

open
53
2021-12-02 20:00:00.000

standby
53
2021-12-02 20:10:00.000

standby
53
2021-12-02 21:00:00.000

down
53
2021-12-02 21:30:00.000

down
53
2021-12-02 22:00:00.000


-- 方法一 :
;WITH CTE01 AS (
SELECT M.[update_at],M.[paragraph],M.[plc_value],
1+SUM(M.YY) OVER (ORDER BY M.[update_at]) AS NO
FROM (
SELECT [history].*,
CASE 
WHEN [paragraph]=LAG([paragraph],1,[paragraph]) OVER (ORDER BY [update_at]) 
THEN 0 ELSE 1 END AS YY
FROM [history]
WHERE [paragraph] = 'open' OR [paragraph] = 'down') AS M),
CTE02 AS (
SELECT CTE01.*,((NO-1) / 2) + 1 AS [QUOTIENT]
FROM CTE01)
--
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at]
FROM (
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at],
ROW_NUMBER() OVER (PARTITION  BY [QUOTIENT] ORDER BY [update_at]) NUN
FROM CTE02) AS XX
WHERE NUN = 1
UNION
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at]
FROM (
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at],
ROW_NUMBER() OVER (PARTITION  BY [QUOTIENT] ORDER BY [update_at] DESC) NUN
FROM CTE02) AS ZZ
WHERE NUN = 1
ORDER BY [update_at]


QUOTIENT
paragraph
plc_value
update_at


1
open
53
2021-12-01 01:00:00.000

1
down
53
2021-12-01 08:00:00.000

2
open
53
2021-12-02 11:00:00.000

2
down
53
2021-12-02 18:00:00.000

3
open
53
2021-12-02 19:00:00.000

3
down
53
2021-12-02 22:00:00.000


-- 方法二 :
;WITH CTE01 AS (
SELECT M.[update_at],M.[paragraph],M.[plc_value],
1+SUM(M.YY) OVER (ORDER BY M.[update_at]) AS NO
FROM (
SELECT [history].*,
CASE 
WHEN [paragraph]=LAG([paragraph],1,[paragraph]) OVER (ORDER BY [update_at]) 
THEN 0 ELSE 1 END AS YY
FROM [history]
WHERE [paragraph] = 'open' OR [paragraph] = 'down') AS M),
CTE02 AS (
SELECT CTE01.*,((NO-1) / 2) + 1 AS [QUOTIENT]
FROM CTE01)
--
SELECT C.*,D.paragraph,D.update_at_down,DATEDIFF(HOUR,C.update_at_open,D.update_at_down) AS HOUR_TEMP
FROM (
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at] AS [update_at_open]
FROM (
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at],
ROW_NUMBER() OVER (PARTITION  BY [QUOTIENT] ORDER BY [update_at]) NUN
FROM CTE02) AS XX
WHERE NUN = 1) AS C
LEFT JOIN (
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at] AS [update_at_down]
FROM (
SELECT [QUOTIENT],[paragraph],[plc_value],[update_at],
ROW_NUMBER() OVER (PARTITION  BY [QUOTIENT] ORDER BY [update_at] DESC) NUN
FROM CTE02) AS ZZ
WHERE NUN = 1
) AS D ON D.[QUOTIENT] = C.[QUOTIENT]
ORDER BY C.[update_at_open]


QUOTIENT
paragraph
plc_value
update_at_open
paragraph
update_at_down
HOUR_TEMP


1
open
53
2021-12-01 01:00:00.000
down
2021-12-01 08:00:00.000
7

2
open
53
2021-12-02 11:00:00.000
down
2021-12-02 18:00:00.000
7

3
open
53
2021-12-02 19:00:00.000
down
2021-12-02 22:00:00.000
3

paragraph	plc_value	update_at
open	53	2021-12-01 01:00:00.000
open	53	2021-12-01 02:00:00.000
open	53	2021-12-01 03:00:00.000
standby	53	2021-12-01 04:00:00.000
standby	53	2021-12-01 05:00:00.000
down	53	2021-12-01 06:00:00.000
down	53	2021-12-01 07:00:00.000
down	53	2021-12-01 08:00:00.000
open	53	2021-12-02 11:00:00.000
open	53	2021-12-02 12:00:00.000
open	53	2021-12-02 13:00:00.000
standby	53	2021-12-02 14:00:00.000
standby	53	2021-12-02 15:00:00.000
down	53	2021-12-02 16:00:00.000
down	53	2021-12-02 17:00:00.000
down	53	2021-12-02 18:00:00.000
open	53	2021-12-02 19:00:00.000
open	53	2021-12-02 20:00:00.000
standby	53	2021-12-02 20:10:00.000
standby	53	2021-12-02 21:00:00.000
down	53	2021-12-02 21:30:00.000
down	53	2021-12-02 22:00:00.000