如何搜索“周围"行?给定的字符串值? [英] How can I search for rows "around" a given string value?

问题描述

我有一个表，其中包含一个名为 identifier 的 varchar() 字段，其中包含实体的唯一名称.

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

我想搜索表并找到具有给定标识符的行该标识符两侧最多 10 行，按字母顺序排序(即，搜索到的标识符及其两侧相邻的行).

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).

在 SQL Server 2005 中表述这个的最佳方法是什么?我猜有一些 ROW_NUMBER() 魔法可以做到这一点，但我没有找到任何可以做这种事情的查询.

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)

该表有超过200万条记录，标识符字段最长可达1000个字符.

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

推荐答案

这个答案对编译器更友好

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

这篇关于如何搜索“周围"行?给定的字符串值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！