文章目录


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

第五十一章 SQL命令 HAVING（二）_database

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

第五十一章 SQL命令 HAVING（二）_database_02

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

第五十一章 SQL命令 HAVING（二）_database_03

​​%INLIST​​谓词可用于对列表结构的元素执行相等比较。

​​%INLIST​​使用​​EXACT​​排序。

因此，默认情况下，​​%INLIST​​字符串比较是区分大小写的。

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

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

第五十一章 SQL命令 HAVING（二）_数据库_04

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

第五十一章 SQL命令 HAVING（二）_字段_05

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

%STARTSWITH谓词

IRIS ​​%STARTSWITH​​比较操作符允许对字符串或数字的初始字符执行部分匹配。

下面的示例使用​​%STARTSWITH​​。

它根据年龄进行选择，然后为每个以“S”开头的Name返回一条记录:

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

第五十一章 SQL命令 HAVING（二）_字段_06

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

第五十一章 SQL命令 HAVING（二）_字段_07

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

第五十一章 SQL命令 HAVING（二）_having_08

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

否则，不返回任何记录。

NULL 谓词

这将检测未定义的值。

你可以检测所有空值，或所有非空值:

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

第五十一章 SQL命令 HAVING（二）_字段_09

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

第五十一章 SQL命令 HAVING（二）_字段_10

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

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

第五十一章 SQL命令 HAVING（二）_数据库_11

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

第五十一章 SQL命令 HAVING（二）_数据库_12

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

第五十一章 SQL命令 HAVING（二）_sql_13