下表列出了给定值的前后行。
例如,希望在给定列的前后显示2行
col2
价值
125
并希望在每个组后打印空白行。
表格:
CREATE TABLE PreTest
(
col1 int,
col2 int,
col3 date,
col4 time
);
样本数据:
insert into PreTest values(111,123,'2018-01-01','00:10:11'),
(111,124,'2018-01-01','00:10:12'),
(111,125,'2018-01-01','00:10:17'),
(111,126,'2018-01-01','00:10:16'),
(111,127,'2018-01-01','00:10:13');
insert into PreTest values(111,228,'2018-01-02','10:13:01'),
(111,229,'2018-01-02','10:13:11'),
(111,125,'2018-01-02','10:13:02'),
(111,237,'2018-01-02','10:13:30'),
(111,232,'2018-01-01','10:13:04');
insert into PreTest values(111,339,'2018-01-03','11:10:01'),
(111,338,'2018-01-03','11:10:04'),
(111,125,'2018-01-03','11:10:02'),
(111,340,'2018-01-03','00:10:11'),
(111,333,'2018-01-01','11:10:03');
WITH C1 AS
(
SELECT ROW_NUMBER() OVER(order by col3,col4) rn,*
FROM PreTest
)
SELECT * FROM
(
SELECT * FROM C1 WHERE rn IN (SELECT rn FROM C1 WHERE col2 = '125')
UNION
SELECT * FROM C1 WHERE rn IN ( SELECT rn - 1 FROM C1 WHERE col2 = '125')
UNION
SELECT * FROM C1 WHERE rn IN ( SELECT rn - 2 FROM C1 WHERE col2 = '125')
UNION
SELECT * FROM C1 WHERE rn IN ( SELECT rn + 1 FROM C1 WHERE col2 = '125')
UNION
SELECT * FROM C1 WHERE rn IN ( SELECT rn + 2 FROM C1 WHERE col2 = '125')
) a
预期产量
:
rn col1 col2 col3 col4
-------------------- ----------- ----------- ---------- ----------------
4 111 127 2018-01-01 00:10:13.0000000
5 111 126 2018-01-01 00:10:16.0000000
6 111 125 2018-01-01 00:10:17.0000000
7 111 132 2018-01-01 10:13:04.0000000
8 111 232 2018-01-01 10:13:04.0000000
12 111 128 2018-01-02 10:13:01.0000000
13 111 130 2018-01-02 10:13:02.0000000
14 111 125 2018-01-02 10:13:02.0000000
15 111 229 2018-01-02 10:13:11.0000000
16 111 129 2018-01-02 10:13:11.0000000
22 111 139 2018-01-03 11:10:01.0000000
23 111 141 2018-01-03 11:10:02.0000000
24 111 125 2018-01-03 11:10:02.0000000
25 111 338 2018-01-03 11:10:04.0000000
26 111 138 2018-01-03 11:10:04.0000000
