第五十一章 SQL命令 HAVING（二）
In和%INLIST谓词
IN谓词用于将值与一系列非结构化的项进行匹配。
%INLIST谓词是 IRIS扩展，用于将值与列表结构的元素进行匹配。
使用任一谓词，都可以执行相等比较和子查询比较。
在中有两种格式。第一个用作使用与OR运算符链接在一起的多个相等比较的速记。例如：
SELECT Name, Home_State FROM Sample.Person
GROUP BY Home_State
HAVING Home_State IN ('ME','NH','VT','MA','RI','CT')
如果Home_State等于括号列表中的任意值，则计算为TRUE。列表元素可以是常量或表达式。排序规则适用于IN比较，因为它适用于相等性测试。默认情况下，IN比较使用字段定义的排序规则类型；默认情况下，字符串字段定义为SQLUPPER，不区分大小写。
当日期或时间用于IN谓词相等比较时，会自动执行适当的数据类型转换。如果HAVING子句字段是TIMESTAMP类型，则DATE或TIME类型的值将转换为TIMESTAMP。如果HAVING子句字段为DATE类型，则TIMESTAMP或STRING类型的值将转换为DATE。如果HAVING子句字段为TIME类型，则TIMESTAMP或STRING类型的值将转换为TIME。
下面的示例都执行相同的相等比较并返回相同的数据。
groupby字段指定对于每个成功的相等比较只返回一条记录。
DOB字段的数据类型为Date:
SELECT Name,DOB FROM Sample.Person
GROUP BY DOB
HAVING DOB IN ({d '2014-01-02'},{d '1990-04-25'})
SELECT Name,DOB FROM Sample.Person
GROUP BY DOB
HAVING DOB IN ({ts '2014-01-02 00:00:00'},{ts '1990-04-25 00:00:00'})
%INLIST谓词可用于对列表结构的元素执行相等比较。
%INLIST使用EXACT排序。
因此，默认情况下，%INLIST字符串比较是区分大小写的。
下面的例子使用%INLIST来匹配一个字符串值到FavoriteColors列表字段的元素:
SELECT Name,FavoriteColors FROM Sample.Person
HAVING 'Red' %INLIST FavoriteColors
它返回FavoriteColors中包含元素“Red”的所有记录。
下面的嵌入式SQL示例将Home_State列值与northne(新英格兰北部各州)列表中的元素匹配:
ClassMethod Having()
{
s northne = $lb("VT","NH","ME")
&sql(
DECLARE StateCursor CURSOR FOR
SELECT Name,Home_State
INTO :name,:state FROM Sample.Person
HAVING Home_State %INLIST :northne
)
&sql(OPEN StateCursor)
q:(SQLCODE'=0)
n %ROWCOUNT,%ROWID
for {
&sql(FETCH StateCursor)
q:SQLCODE
w !,"#",%ROWCOUNT," Name=",name," State=",state,!
}
w !,"Final Fetch SQLCODE: ",SQLCODE
&sql(CLOSE StateCursor)
}
DHC-APP>d ##class(PHA.TEST.SQLCommand).Having()
#1 Name=Lepon,Jeff Z. State=NH
#2 Name=Ingleman,Terry A. State=NH
#3 Name=Jung,Keith W. State=NH
#4 Name=Xiang,Kirsten U. State=ME
#5 Name=Jackson,Ralph V. State=VT
#6 Name=Tesla,Geoffrey O. State=NH
还可以在子查询中使用IN或%INLIST来测试列值(或任何其他表达式)是否等于任何子查询行值。
例如:
SELECT Name,Home_State FROM Sample.Person
HAVING Name IN
(SELECT Name FROM Sample.Employee
HAVING Salary < 50000)
注意，子查询在SELECT列表中必须只有一个项。
%STARTSWITH谓词
IRIS %STARTSWITH比较操作符允许对字符串或数字的初始字符执行部分匹配。
下面的示例使用%STARTSWITH。
它根据年龄进行选择，然后为每个以“S”开头的Name返回一条记录:
SELECT Name,Age FROM Sample.Person
WHERE Age > 30
HAVING Name %STARTSWITH 'S'
ORDER BY Name
与其他字符串字段比较一样，%STARTSWITH比较不区分大小写。
Contains Operator ([)
Contains操作符是左括号符号:[。
它允许将子字符串(字符串或数字)匹配到字段值的任何部分。
比较总是区分大小写的。
下面的例子在HAVING子句中使用Contains操作符选择那些Home_State值包含“K”的记录，然后对这些状态执行%AFTERHAVING计数:
SELECT Home_State,COUNT(Home_State) AS States,
COUNT(Home_State %AFTERHAVING) AS KStates
FROM Sample.Person
HAVING Home_State [ 'K'
FOR SOME谓词
HAVING子句的FOR SOME谓词决定是否根据一个或多个字段值的条件测试返回结果集。
该谓词的语法如下:
FOR SOME (table[AS t-alias]) (fieldcondition)
FOR SOME指定字段condition的值必须为true;
至少有一个字段值必须匹配指定的条件。
Table可以是单个表，也可以是逗号分隔的表列表，也可以是表别名。
Fieldcondition为指定表中的一个或多个字段指定一个或多个条件。
table参数和字段condition参数都必须用括号分隔。
下面的例子展示了FOR SOME谓词的用法:
SELECT Name,Age
FROM Sample.Person
HAVING FOR SOME (Sample.Person)(Age>20)
ORDER BY Age
在上面的示例中，如果至少有一个字段包含大于20的Age值，则返回所有记录。
否则，不返回任何记录。
NULL 谓词
这将检测未定义的值。
你可以检测所有空值，或所有非空值:
SELECT Name, FavoriteColors FROM Sample.Person
HAVING FavoriteColors IS NULL
SELECT Name, FavoriteColors FROM Sample.Person
HAVING FavoriteColors IS NOT NULL
ORDER BY FavoriteColors
使用GROUP BY子句，可以为指定字段的每个非空值返回一条记录:
SELECT Name, FavoriteColors FROM Sample.Person
GROUP BY FavoriteColors
HAVING FavoriteColors IS NOT NULL
ORDER BY FavoriteColors
EXISTS 谓词
它使用子查询来测试子查询是否计算为空集。
SELECT t1.disease FROM illness_tab t1 WHERE EXISTS
(SELECT t2.disease FROM disease_registry t2
WHERE t1.disease = t2.disease
HAVING COUNT(t2.disease) > 100)
LIKE、%MATCHES和%PATTERN谓词
这三个谓词允许执行模式匹配。
-
LIKE允许使用文字和通配符进行模式匹配。
当希望返回包含已知字面值子字符串的数据值，或在已知序列中包含多个已知子字符串时，请使用LIKE。
LIKE使用目标的排序规则进行字母大小写比较。 -
%MATCHES允许使用文字、通配符、列表和范围进行模式匹配。
当希望返回包含已知字面值子字符串的数据值，或包含一个或多个位于可能字符列表或范围内的字面值字符，或在已知序列中包含多个这样的子字符串时，请使用%MATCHES。
%MATCHES使用EXACT排序法进行字母大小写比较。 - 允许指定字符类型的模式。
例如,'1U4L1",".A'(1个大写字母，4个小写字母，一个逗号，后面跟着任意数量的字母字符)。
如果希望返回包含已知字符类型序列的数据值，请使用%PATTERN。
当数据值不重要，但这些值的字符类型格式很重要时，%PATTERN特别有用。
PATTERN还可以指定已知的文字字符。
它使用EXACT排序法进行文字比较，这总是区分大小写的。
要与字符串的第一个字符进行比较，请使用%STARTSWITH谓词。
示例
下面的示例为每个至少有一个21岁以下的人的州返回一行。
对于每一行，它返回该州所有人的平均、最小和最大年龄。
SELECT Home_State, MIN(Age) AS Youngest,
AVG(Age) AS AvgAge, MAX(Age) AS Oldest
FROM Sample.Person
GROUP BY Home_State
HAVING Age < 21
ORDER BY Youngest
下面的示例为每个至少有一个21岁以下的人的州返回一行。
对于每一行，它返回该州所有人的平均、最小和最大年龄。
使用%AFTERHAVING关键字，它还返回该州21岁以下的人的平均年龄(AvgYouth)，以及该州21岁以下最年长的人的年龄(OldestYouth)。
SELECT Home_State,AVG(Age) AS AvgAge,
AVG(Age %AFTERHAVING) AS AvgYouth,
MIN(Age) AS Youngest, MAX(Age) AS Oldest,
MAX(Age %AFTERHAVING) AS OldestYouth
FROM Sample.Person
GROUP BY Home_State
HAVING Age < 21
ORDER BY AvgAge
