查询结果中的一系列记录 [英] query for a range of records in result

问题描述

我想知道是否有一些简单的方法、函数或其他方法可以从具有以下结果的查询中返回数据.

I am wondering if there is some easy way, a function, or other method to return data from a query with the following results.

我有一个 SQL Express DB 2008 R2，一个在给定列中包含数字数据的表，比如 col T.

I have a SQL Express DB 2008 R2, a table that contains numerical data in a given column, say col T.

我在代码中得到一个值 X，我想返回最多三个记录.col T 等于我的值 X 的记录，以及之前和之后的记录，仅此而已.排序在 col T 上完成.之前的记录可能是文件的开头，因此不存在，同样，如果 X 等于最后一条记录，则后面的记录将不存在，文件/表的结尾.

I am given a value X in code and would like to return up to three records. The record where col T equals my value X, and the record before and after, and nothing else. The sort is done on col T. The record before may be beginning of file and therefore not exist, likewise, if X equals the last record then the record after would be non existent, end of file/table.

表中可能不存在 X 的值.

The value of X may not exist in the table.

我认为这类似于按数字顺序获得一系列结果.

This I think is similar to get a range of results in numerical order.

对于解决此问题的任何帮助或指导，我们将不胜感激.

Any help or direction in solving this would be greatly appreciated.

再次感谢，

推荐答案

这可能不是最佳解决方案，但是:

It might not be the most optimal solution, but:

SELECT T
FROM theTable
WHERE T = X
UNION ALL
SELECT *
FROM 
(
    SELECT TOP 1 T
    FROM theTable
    WHERE T > X
    ORDER BY T
) blah
UNION ALL
SELECT *
FROM
(
    SELECT TOP 1 T
    FROM theTable
    WHERE T < X
    ORDER BY T DESC
) blah2

这篇关于查询结果中的一系列记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！