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.

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

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.

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.

Thanks again,

回答1:

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

回答2:

DECLARE @x int = 100

;WITH t as 
(
    select ROW_NUMBER() OVER (ORDER BY T ASC) AS row_nm,*
    from YourTable
)
, t1 as
(
    select *
    from t
    WHERE T = @x
)
select *
from t 
CROSS APPLY t1
WHERE t.row_nm BETWEEN t1.row_nm -1 and t1.row_nm + 1