How can I search for rows “around” a given string

问题:

I have a table with a varchar() field called identifier that contains a unique name for the entity.

I'd like to search the table and find the rows with a given identifier and up to 10 rows on either side of that identifier, sorted alphabetically (i.e., the searched-for identifier and its neighboring rows on either side).

What is the best way to formulate this in SQL Server 2005? I'm guessing there's some ROW_NUMBER() magic that can do this, but I'm not finding any go-by queries to do this sort of thing.

This is as close as I can get so far, but performance is terrible:

WITH 
  allrows AS (
   SELECT *, ROW_NUMBER() OVER(ORDER BY identifier DESC) AS RN
       FROM mytable
  ),
  centerrow AS (
    SELECT RN AS CRN FROM allrows WHERE identifier = 'MyValue'
  )
SELECT * FROM allrows, centerrow 
WHERE RN BETWEEN (centerrow.CRN - 10) AND (centerrow.CRN + 10)

The table has over 2 million records, and the identifier field can be as long as 1000 characters.

回答1:

This answer is a bit more compilerfriendly

declare @e table(identifier varchar(5))
insert @e values (0),(1),(2),(3),(4)

SELECT * FROM(
SELECT top 2 * 
FROM @e WHERE 
identifier >= '2' 
ORDER BY identifier asc) a
UNION ALL 
SELECT * FROM (
SELECT top 1 * 
FROM @e 
WHERE identifier < '2' 
ORDER BY identifier desc ) b

回答2:

SELECT TOP 11 * FROM MyTable WHERE identifier >= 'My Value' ORDER BY identifier ASC
UNION ALL
SELECT TOP 10 * FROM MyTable WHERE identifier < 'My Value' ORDER BY identifier DESC