第五十一章 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')

image.png

如果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'})

image.png

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'})

image.png

%INLIST谓词可用于对列表结构的元素执行相等比较。
%INLIST使用EXACT排序。
因此，默认情况下，%INLIST字符串比较是区分大小写的。

下面的例子使用%INLIST来匹配一个字符串值到FavoriteColors列表字段的元素:

SELECT Name,FavoriteColors FROM Sample.Person 
HAVING 'Red' %INLIST FavoriteColors

image.png

它返回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)

image.png

注意，子查询在SELECT列表中必须只有一个项。

%STARTSWITH谓词

IRIS %STARTSWITH比较操作符允许对字符串或数字的初始字符执行部分匹配。
下面的示例使用%STARTSWITH。
它根据年龄进行选择，然后为每个以“S”开头的Name返回一条记录:

SELECT Name,Age FROM Sample.Person
WHERE Age > 30
HAVING Name %STARTSWITH 'S'
ORDER BY Name

image.png

与其他字符串字段比较一样，%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'

image.png

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

image.png

在上面的示例中，如果至少有一个字段包含大于20的Age值，则返回所有记录。
否则，不返回任何记录。

NULL 谓词

这将检测未定义的值。
你可以检测所有空值，或所有非空值:

SELECT Name, FavoriteColors FROM Sample.Person
HAVING FavoriteColors IS NULL 

image.png

SELECT Name, FavoriteColors FROM Sample.Person
HAVING FavoriteColors IS NOT NULL 
ORDER BY FavoriteColors

image.png

使用GROUP BY子句，可以为指定字段的每个非空值返回一条记录:

SELECT Name, FavoriteColors FROM Sample.Person
GROUP BY FavoriteColors
HAVING FavoriteColors IS NOT NULL 
ORDER BY FavoriteColors

image.png

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

image.png

下面的示例为每个至少有一个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

image.png