按字母顺序获取结果范围的SQL查询

Question

我有一个表格tblTags，它的工作方式与StackOverflow的标签系统类似。

当我查看标签页面时，比如说标签Tutorial，我想要按字母顺序显示它前后的10个标签。

所以，如果我们有ID为30的标签Tutorial，我们如何返回一个类似以下顺序的记录集：

Tap
Tart
> Tutorial
Umbrellas
Unicorns
Xylaphones

我想到了一些方法来实现这个目标，但在我看来它们都不太好，因为它们涉及检索大量丑陋的数据。

我不确定是否可以按以下方式进行操作（伪代码）：

SELECT RANGE(0 - 30) FROM tblTags ORDER BY Name ASC

然而，如果不遍历整个列表，您如何以高效的方式知道教程标签在列表中的位置呢？

如果使用SQL Server 2008 R2 Express和LINQ，查询或LINQ是很好的答案。谢谢！

- Tom Gullen

---

@Teo.sk 我们知道ID和名称。 - Tom Gullen

@Teo.sk BETWEEN 看起来不错！但我需要确保它能够保证获取合理数量的记录，因为根据标签的数量/类型，between 可能会返回过多或过少的记录。 - Tom Gullen

我认为下面的联合解决方案将根据您的表格大小给出良好的结果。英语单词不到200K，因此我不会想象您的表格会如此大，以至于大小会影响性能（除非您允许部分单词作为标签）。此外，为了获得最佳性能，请对名称列进行索引。 - cordsen

@Cordsen，谢谢，我在其他答案的帮助下成功地实现了一个工作的linq解决方案！ - Tom Gullen

Answer 1

也许使用联合体可以解决问题。（我确定我有一些语法错误，但你可以理解我的意思）

（编辑：感谢评论和其他答案，特别是rsbarro的答案）

---

DECLARE @tags AS TABLE(TagID INT, tag VARCHAR(30))
INSERT INTO @tags VALUES(1, 'a')
INSERT INTO @tags VALUES(2, 'b')
INSERT INTO @tags VALUES(3, 'c')
INSERT INTO @tags VALUES(4, 'd')
INSERT INTO @tags VALUES(5, 'e')
INSERT INTO @tags VALUES(6, 'f')
INSERT INTO @tags VALUES(7, 'g')
INSERT INTO @tags VALUES(8, 'h')
INSERT INTO @tags VALUES(9, 'i')
INSERT INTO @tags VALUES(10, 'j');

select * from (select top(2) tag
from @tags
where tag < 'f'
order by tag desc
) a

union

select * from (select top(3) tag
from @tags
where tag >= 'f'
order by tag) b;

然而，我会进行性能检查，以确定这是否比返回更多行再进行过滤更快。我有一种感觉，有一种更高效的方法。

- Jacob Eggers

---

+1 我喜欢这种方法。你可能会得到与我的表变量方法类似的性能（也许稍微好一些，因为你不必在表上再次进行连接，但如果所有内容都正确索引，对于小数据集可能没有区别）。不过，看到有人用一个 select 做到这一点会很有趣。=] - rsbarro

Answer 2

在Jacob的UNION建议的基础上，您可以使用一个表变量将匹配的TagID选择出来，然后再与Tag表连接以获取匹配的记录。虽然不够优雅，但确实可行。

顺便说一句，我认为UNION方法也可以工作，但据我所知，SQL Server只允许在最后一个SELECT上使用ORDER BY，而且该ORDER BY适用于整个结果集（此帖子也指出了同样的事情）。

DECLARE @tags AS TABLE(TagID INT, Name VARCHAR(30))
INSERT INTO @tags VALUES(1, 'a')
INSERT INTO @tags VALUES(2, 'b')
INSERT INTO @tags VALUES(3, 'c')
INSERT INTO @tags VALUES(4, 'd')
INSERT INTO @tags VALUES(5, 'e')
INSERT INTO @tags VALUES(6, 'f')
INSERT INTO @tags VALUES(7, 'g')
INSERT INTO @tags VALUES(8, 'h')
INSERT INTO @tags VALUES(9, 'i')
INSERT INTO @tags VALUES(10, 'j')

DECLARE @selectedTags AS TABLE(TagID INT)
INSERT INTO @selectedTags
SELECT TOP 2 TagID FROM @tags WHERE Name < 'e' ORDER BY Name DESC
INSERT INTO @selectedTags
SELECT TOP 2 TagID FROM @tags WHERE Name >= 'e' ORDER BY Name

SELECT * 
FROM @tags T
JOIN @selectedTags ST ON ST.TagID = T.TagID
ORDER BY T.Name

- rsbarro

Answer 3

正如Jacob所建议的那样，我也有同样的想法，只是SQL Server会使用“top 10”而不是limit。

select top 3 * 
from names
where name<'d'
union
select top 3 * 
from names
where name>='d'
order by name 

（本人在SQL Server 2008 R2上测试过此方法）

编辑： 正如正确指出的那样，先前的查询未能提供所需的结果。然而，这个查询应该可以，不过可能有更有效的方法来完成同样的事情。

select name from names
where name in (
    select top 3 name from names where name<'e' order by name desc
    )or name in (
    select top 3 name from names where name>='e')
order by name 

- trailmax

---

不幸的是，这并不起作用。假设您的表格包含'a'到'j'，并且您对2条记录中的'e'运行它。您将会得到'a'、'b'、'e'和'f'。 - rsbarro

@rsbarro 你说得完全正确！不幸的是，SQL Server 不允许在 Union 连接中使用两个 "order by" 子句。 - trailmax

@rsbarro请查看更新的帖子。与您的想法类似，但不使用临时表。 - trailmax

Answer 4

With X As (select tblTag.*, Row_Number() Over(Order By tag) R_NUMB From tblTag )
Select tag From X
Where X.R_NUMB  Between (Select X.R_NUMB From X  Where tag = 'Tutorial') - 10 
And (Select X.R_NUMB From X  Where tag = 'Tutorial') + 10

- LNO

Answer 5

花了我一段时间才弄明白，感谢联合想法。不太确定为什么这个方法有效，.take(n) 似乎需要一个很大的数字才能工作，不太确定原因是什么，但这段代码似乎完全正常，每边将取5个。

var q = (
    from c in db.tblTags
    where
        !(from o in db.tblTagSynonyms
            select o.SynonymTagID)
        .Contains(c.ID)
        &&
        c.Name.CompareTo(AnchorTagName) < 1
    orderby c.Name descending
    select new
        {
            c.ID,
            c.Name,
            c.Priority,
            TotalResources = (from a in db.tblTagAnchors where a.TagID == c.ID select new { a.ID }).Count()
        }
    ).Take(7).ToArray().Reverse().Union(
    from c in db.tblTags
    where
        !(from o in db.tblTagSynonyms
            select o.SynonymTagID)
        .Contains(c.ID)
        &&
        c.Name.CompareTo(AnchorTagName) >= 1
    orderby c.Name ascending
    select new
    {
        c.ID,
        c.Name,
        c.Priority,
        TotalResources = (from a in db.tblTagAnchors where a.TagID == c.ID select new { a.ID }).Count()
    }
).Skip(1).Take(11).ToArray();

- Tom Gullen