文章
姚 鑫 · 十月 20 阅读大约需 6 分钟

第五十一章 SQL命令 HAVING(二)

第五十一章 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类型,则DATETIME类型的值将转换为TIMESTAMP。如果HAVING子句字段为DATE类型,则TIMESTAMPSTRING类型的值将转换为DATE。如果HAVING子句字段为TIME类型,则TIMESTAMPSTRING类型的值将转换为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

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

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
00
1 0 0 4
Log in or sign up to continue