SQL每日一题(20210811)

有如下1张表T0811

 希望得到如下结果（根据TransType取每道OprSeq的开始与结束时间，其中，开始-换班是一组，开始-结束是一组)

测试数据

create table T0811
(
  transtype VARCHAR2(22),
  oprseq    INTEGER,
  opcode    VARCHAR2(24),
  transtime VARCHAR2(34)
);
INSERT INTO "ODS0812"."T0811" VALUES ('开始', '10', 'NF21', '2019-11-30 14:06');
INSERT INTO "ODS0812"."T0811" VALUES ('换班', '10', 'NF21', '2019-11-30 14:09');
INSERT INTO "ODS0812"."T0811" VALUES ('开始', '10', 'NF21', '2019-11-30 14:10');
INSERT INTO "ODS0812"."T0811" VALUES ('结束', '10', 'NF21', '2019-11-30 14:13');
INSERT INTO "ODS0812"."T0811" VALUES ('开始', '20', 'NF22', '2019-11-30 14:15');
INSERT INTO "ODS0812"."T0811" VALUES ('结束', '20', 'NF22', '2019-11-30 14:16');
INSERT INTO "ODS0812"."T0811" VALUES ('开始', '30', 'NF24', '2019-11-30 14:17');
INSERT INTO "ODS0812"."T0811" VALUES ('结束', '30', 'NF24', '2019-11-30 14:20');

查询语句：

--查询语句
WITH CTE AS ( SELECT t0811.*, ROW_NUMBER ( ) OVER ( PARTITION BY Oprseq ORDER BY Transtime ) RN FROM t0811 
) 
SELECT
a.oprseq,
a.Opcode,
a.transtime AS StartTime,
b.transtime AS EndtTime,
a.RN 
FROM
	CTE a
	LEFT JOIN CTE b ON b.Oprseq = a.Oprseq 
	AND b.RN = a.RN + 1 
WHERE
	mod( a.RN, 2 ) > 0;