SQL-在A-F之间查找名称的条件 [英] SQL - Where criteria to find names between A-F

问题描述

一个简单的问题:

我需要一个解决方案，以便可以在A-F之间查找名称，包括所有以F开头的名称.

I need a solution so that I can find, lets say names, between A-F, INCLUDING all names that start with F.

如果您使用BETWEEN或A> =值< = F，您会发现它停在F位置.因此，我将其发布为建议.

If you use BETWEEN or A >= value <= F you find out that it stops at F. So I am posting this for suggestions.

注意: 用户将看到2个文本框，接受用户可以键入的范围.用户可以像这样细化F边界的距离:用户键入"Fa"表示结果应返回:Fauder，Fail，Famber等...

NOTE: User will see 2 textboxes that accept a range user can type. The user refines how far to go in F boundary as such: User types in 'Fa' means the result should return: Fauder, Fail, Famber, ... etc

我目前有2种解决方案，但是有一种更好的方法.

I have currently 2 solutions but there's got a be a better way.

解决方案1: 这将在外边界加1，但如果名称为单个"G"，则可能会包含结果，尽管可能性很小. 名称> ='A'AND< = CHAR(ASCII('F')+ 1)

Solution 1: This will add 1 to outer boundary but may include result if there's a name that is single 'G', highly unlikely though. WHERE name >= 'A' AND <= CHAR(ASCII('F') + 1)

解决方案2: 此解决方案附加了字母字段长度时间的最后一个字母. 名称> ='A'并且< ='FZZZZZZZZZZZZZZZZZZZZZZZ'

Solution 2: This solution appends last letter of alphabet field length times. WHERE name >= 'A' AND <= 'FZZZZZZZZZZZZZZZZZZZZZ'

尽管以上解决方案都是可行的，但我的搜索可以这样进行细化: A到Fs(应该给我从A到Fs的所有信息……).通过此解决方案，#1由于可与单个ASCII一起使用而被破坏.

Although the above solutions are workable, my search can be refined as such: A to Fs (should give me everything from A to and including Fs....). With this solution #1 is broken since it works with single ascii.

欢迎提出建议.

推荐答案

在注释中，您将要求扩展为包括A-Fxxx.

In a comment you expand the requirement to include A - Fxxx.

SET @start = 'A'
SET @end   = 'Fxxx'

SELECT
  *
FROM
  table
WHERE
  (name >= @start AND name < @end)
  OR (name LIKE @end + '%')

请注意，这不包括name字段上的功能；此类功能可防止在索引上进行范围查找.但是，包含OR也会降低索引搜索.尽管这是额外的代码，但在某些情况下，这实际上可以提高性能……

Note that this does not include functions on the name field; such functions prevent range seeks on indexes. However, the inclusion of the OR degrades the index seek too. Although it's extra code, this actually can be more performant is some cases...

SELECT
  *
FROM
  table
WHERE
  (name >= @start AND name < @end)

UNION ALL

SELECT
  *
FROM
  table
WHERE
  (name LIKE @end + '%')

编辑

事后看来，您的 Solution 2 尽管不喜欢，但可能是最好的.

In hindsight, your Solution 2, although you don't like it, is probably the best.

使用STUFF()将Fxxx转换为FxxxZZZZZZZZZZ很简单，只要您知道最大字符串长度，就应该使用数据库，这是您应该做的.

Turning Fxxx to FxxxZZZZZZZZZZ is simple enough with STUFF(), provided that you know the max string length, which you should do as the database is yours.

它在name字段上没有任何功能，并且在WHERE子句中不使用OR.这意味着您可以对任何索引进行纯范围搜索.

It doesn't have any functions on the name field, and it doesn't use an OR in the WHERE clause. Which means that you get a pure range seek on any index.

在性能方面比较明智，我认为您无法对此进行改进.

Performance wise, I do not think you can improve on this.

这篇关于SQL-在A-F之间查找名称的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！