sql-server - 在记录条目之前和之后查找
问题描述
我有下表来查找值之前和之后的给定值。
例如,要在给定列col2
value的前后显示 1 个值125
。
桌子:
CREATE TABLE PreTest1
(
col1 int,
col2 int,
col3 date,
col4 time
);
样本数据:
insert into PreTest1 values(111,124,'2018-01-01','00:10:11'),
(111,124,'2018-01-01','00:10:12'),
(111,122,'2018-01-01','00:10:17'),
(111,125,'2018-01-01','00:10:16'),
(111,125,'2018-01-01','00:10:13'),
(111,123,'2018-01-01','00:10:19'),
(111,130,'2018-01-01','00:10:18'),
(111,123,'2018-01-01','00:10:17'),
(111,121,'2018-01-01','00:09:11');
询问:
WITH C1 AS
(
SELECT ROW_NUMBER() OVER(order by col2,col3,col4) rn,*
FROM PreTest1
)
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 + 1 FROM C1 WHERE col2 = '125')
) a
预期输出:
col1 col2 col3 col4
----------- ----------- ---------- ----------------
111 124 2018-01-01 00:10:12.0000000
111 125 2018-01-01 00:10:16.0000000
111 123 2018-01-01 00:10:17.0000000
解决方案
像这样的东西?
WITH TMP1 AS
(
SELECT ROW_NUMBER() OVER(partition by col1, col2 order by col3 desc, col4 desc) rang1, *
FROM PreTest1
),
TMP2 as (
select ROW_NUMBER() OVER(order by col3 , col4, col2 desc) rang2, *
from tmp1 where rang1=1
),
ValueRang as
(
SELECT rang2 Position FROM TMP2 where col2=125
)
select * from tmp2
cross apply ValueRang
where tmp2.rang2 between Position-1 and Position + 1
推荐阅读
- django - Django:缓存会话与简单缓存之间的区别?
- java - 如何使用@Query注解在spring data jpa中插入自定义查询
- hibernate - 了解 Kotlin 枚举转换器
- python - 用户无权查询 bigquery 表
- wordpress - 在 WordPress 添加 ical 事件时,收到未知的 HTTP 状态
- javascript - 有什么替代方法:可见
- c++ - 构建张量流时未找到 api_def.pb.h
- karate - 如何将每个 API 调用的日志存储在单独的文本文件中
- office365 - Office 365 重要事件
- html - 尝试从 URL 中删除 .html 并在转到带有子文件夹的页面时收到 403 错误