#
第五章 SQL谓词 BETWEEN # 大纲 ``` scalar-expression BETWEEN lowval AND highval ``` # 参数 - `scalar-expression` - 一种标量表达式(最常见的是数据列),将其值与低值和高值(包括高值)之间的值范围进行比较。 - `lowval` - 解析为低排序规则序列值的表达式,指定与标量表达式中的每个值匹配的值范围的开始。 - `highval` - 解析为高排序规则序列值的表达式,指定要与标量表达式中的每个值匹配的值范围的末尾。 # 描述 `BETWEEN`谓词允许选择`lowval`和`highval`指定范围内的数据值。 这个范围包括低值和高值本身。 这等价于一对大于或等于操作符和一对小于或等于操作符。 下面的例子展示了这种比较: ```sql SELECT Name,Age FROM Sample.Person WHERE Age BETWEEN 18 AND 21 ORDER BY Age ``` 这将返回`Sample`中的所有记录。 年龄值介于`18`到`21`之间的人员表,包括这些值。 注意,必须按升序指定`BETWEEN`值; 例如`BETWEEN 21 AND 18`这样的谓词将返回空字符串。 如果标量表达式的值都不在指定的范围内,则`BETWEEN`返回空字符串。 与大多数谓词一样,`BETWEEN`可以使用`NOT`逻辑运算符进行反转。 `BETWEEN`和`NOT BETWEEN`都不能用于返回`NULL`字段。 返回`NULL`字段使用`IS NULL`。 `NOT BETWEEN`的示例如下: ```sql SELECT Name,Age FROM Sample.Person WHERE Age NOT BETWEEN 20 AND 55 ORDER BY Age ``` 这将返回`Sample`中的所有记录。 年龄值小于`20`或大于`55`的人表,不包括这些值。 # 排序类型 `BETWEEN`通常用于按数字顺序排序的数值范围。 但是,`BETWEEN`可用于任何数据类型值的排序规则序列范围。 `BETWEEN`使用与它所匹配的列相同的排序规则类型。 默认情况下,字符串数据类型排序为`SQLUPPER`,这是不区分大小写的。 如果查询为列分配了不同的排序规则类型,则还必须将此排序规则类型应用于`BETWEEN`子字符串。 下面的例子说明了这一点: 在下面的示例中,`BETWEEN`使用字段的默认字母大小写排序规则`SQLUPPER`,它不区分大小写。 它返回`Name`的字母顺序比`Home_State`高,`Home_State`的字母顺序比`Home_City`高的记录: ```sql SELECT Name,Home_State,Home_City FROM Sample.Person WHERE Home_State BETWEEN Name AND Home_City ORDER BY Home_State ``` 在下例中,`BETWEEN`字符串比较不区分大小写,因为`Home_State`字段被定义为`SQLUPPER`。 这意味着低`val`和高`val`在功能上是相同的,在任何字母中选择`'MA'`: ```sql SELECT Name,Home_State FROM Sample.Person WHERE Home_State BETWEEN 'MA' AND 'Ma' ORDER BY Home_State ``` 在下面的示例中,`%SQLSTRING`排序函数使`BETWEEN`字符串比较区分大小写。 它选择那些`Home_State`值为`'MA'`到`'MA'`的记录,在这个数据集中包括`'MA'`, `'MD'`, `'ME'`, `'MO'`, `'MS'`和`'MT':` ```sql SELECT Name,Home_State FROM Sample.Person WHERE %SQLSTRING(Home_State) BETWEEN %SQLSTRING('MA') AND %SQLSTRING('Ma') ORDER BY Home_State ``` 在以下示例中,`BETWEEN`字符串比较不区分大小写,并且忽略空格和标点符号: ```sql SELECT Name FROM Sample.Person WHERE %STRING(Name) BETWEEN %SQLSTRING('OA') AND %SQLSTRING('OZ') ORDER BY Name ``` 下面的示例显示了在内部连接操作`ON`子句中使用的BETWEEN。 它正在执行一个不区分大小写的字符串比较: ```sql SELECT P.Name AS PersonName,E.Name AS EmpName FROM Sample.Person AS P INNER JOIN Sample.Employee AS E ON P.Name BETWEEN 'an' AND 'ch' AND P.Name=E.Name ``` # %SelectMode 如果`%SelectMode`设置为逻辑格式以外的值,那么`BETWEEN`谓词值必须以`%SelectMode`格式(`ODBC`或`Display`)指定。 这主要适用于日期、时间和 IRIS格式列表(`%List`)。 以逻辑格式指定谓词值通常会导致`SQLCODE`错误。 例如,`SQLCODE -146`“无法将日期输入转换为有效的逻辑日期值”。 在下面的动态SQL示例中,BETWEEN谓词必须以`%SelectMode=1` (ODBC)的格式指定日期: ```sql ClassMethod Between() { s q1 = "SELECT Name,DOB FROM Sample.Person " s q2 = "WHERE DOB BETWEEN '1950-01-01' AND '1960-01-01'" s myquery = q1_q2 s tStatement = ##class(%SQL.Statement).%New() s tStatement.%SelectMode=1 s qStatus = tStatement.%Prepare(myquery) if qStatus'=1 { w "%Prepare failed:" d $System.Status.DisplayError(qStatus) q } s rset = tStatement.%Execute() d rset.%Display() w !,"End of data" } ``` ```java DHC-APP>d ##class(PHA.TEST.SQLCommand).Between() Name DOB Houseman,Martin D. 1955-09-25 Ingrahm,Yan S. 1954-06-15 Smith,Elvis Y. 1955-06-29 Gore,Alfred M. 1958-09-15 Yoders,Liza U. 1959-06-05 Ng,Liza Z. 1955-10-05 Yeats,Debby G. 1951-12-06 Zweifelhofer,Zelda J. 1954-02-19 Solomon,Emily D. 1953-01-28 Isaacs,Elvis V. 1952-04-05 Pantaleo,Robert U. 1950-03-29 Zampitello,Josephine Q. 1953-08-14 Xiang,Molly F. 1953-03-21 Nichols,Heloisa M. 1957-07-19 Hertz,Uma C. 1954-07-25 LaRocca,David X. 1956-01-11 Houseman,Alice R. 1957-12-07 Alton,Phil T. 1953-02-25 Davis,Jane E. 1953-07-28 Vanzetti,Alexandra O. 1953-12-29 Uhles,Dmitry P. 1951-08-23 Jafari,Christine Z. 1950-04-11 22 Rows(s) Affected End of data ```