第九章 SQL谓词 FOR SOME %ELEMENT
将列表元素值或列表元素的数量与谓词匹配。
大纲
FOR SOME %ELEMENT(field) [[AS] e-alias] (predicate)
参数
-
field- 将其元素与谓词进行比较的标量表达式(最常见的是数据列)。 -
AS e-alias- 可选-用于限定谓词中的%KEY或%VALUE的元素别名。通常，当谓词包含嵌套的FOR某些%ELEMENT条件时，会使用此别名。别名必须是有效的标识符。 -
(predicate)- 用括号括起来的谓词条件。
在这个条件中，使用%VALUE和/或%KEY来确定条件匹配的是什么。
%VALUE匹配元素值(%VALUE= ' Red ')。
%KEY匹配元素的最小数目(%KEY=2)。
在此条件下，如果您指定了e-alias，%VALUE和%KEY可能是可选限定的。
这个谓词可以由多个带有AND和OR逻辑运算符的条件表达式组成。
描述
FOR SOME %ELEMENT谓词将字段中的列表元素与指定的谓词匹配。
SOME关键字指定字段中至少有一个元素必须满足指定的谓词子句。
谓词子句必须包含%VALUE或%KEY关键字，后跟谓词条件。
这些关键字不区分大小写。
下面的例子解释了%VALUE和%KEY的用法:
-
(%VALUE=’Red’)匹配所有包含值Red作为其列表元素之一的字段值。
该字段可以只包含单个元素Red，也可以包含多个元素，其中一个元素是Red。 -
(%KEY=2)匹配所有包含至少2个元素的字段值。
字段可以包含两个元素，也可以包含两个以上的元素。
%KEY值必须为正整数。
(%KEY=0)不匹配任何字段值。
FOR SOME %ELEMENT 不能用于匹配为空的字段。
谓词子句可以使用任何谓词条件，而不仅仅是相等条件。以下是谓词子句的一些示例：
(%VALUE='Red')
(%VALUE > 21)
(%VALUE %STARTSWITH 'R')
(%VALUE [ 'e')
(%VALUE IN ('Red','Blue')
(%VALUE IS NOT NULL)
(%KEY=3)
(%KEY > 1)
(%KEY IS NOT NULL)
注意:当在运行时提供谓词值时(使用?
输入参数或:var输入主机变量)，结果谓词%STARTSWITH 'abc'提供了比等价的结果谓词'abc%'更好的性能。
可以使用AND、OR和NOT逻辑操作符指定多个谓词条件。
IRIS将组合的谓词条件应用于每个元素。
因此，使用AND测试应用两个%VALUE或两个%KEY谓词是没有意义的。
例如，使用For SOME %ELEMENT匹配包含值Red、Green、Red Green、Black Red、Green Yellow Red、Green Black、Yellow或Black Yellow的字段:
-
(%VALUE='Red')匹配任何包含元素Red:Red、Red Green、Black Red和Red Yellow Green的字段。 -
(%VALUE='Red' OR %VALUE='Green')匹配任何包含其中一个元素(或同时包含两个元素，按任意顺序)的字段:Red,Green,Red Green,Black Red,Green Yellow Red,Green Black。
这在功能上与(%VALUE IN('Red'，'Green'))相同。 -
(%VALUE='Red' AND %VALUE='Green')不匹配字段值，因为它同时匹配Red和Green的每个元素，并且没有元素可以同时拥有Red和Green的值。
此谓词不匹配双元素值Red Green。 -
(%VALUE='Red' AND %KEY=2)匹配Red Green,Black Red,Green Yellow Red. -
(%value=‘Red’或%key=2)匹配Red,Red Green,Black Red,Green Yellow Red,Green Black,Black Yellow.
FOR SOME %ELEMENT是一个集合谓词。
它可以用于可以指定谓词条件的大多数上下文中，如本手册的谓词概述页面所述。
受以下限制:
- 不能在
HAVING子句中使用FOR SOME %ELEMENT。 - 不能使用
FOR SOME %ELEMENT作为为JOIN操作选择字段的谓词。 - 如果两个谓词引用不同表中的字段，则不能使用
OR逻辑操作符将FOR SOME %ELEMENT与另一个谓词条件关联。
例如:
WHERE FOR SOME %ELEMENT(t1.FavoriteColors) (%VALUE='purple') OR t2.Age < 65
因为此限制取决于优化器如何使用索引，所以SQL可能只在将索引添加到表时执行此限制。
强烈建议在所有查询中避免这种类型的逻辑。
- 在查询分片表时，不能使用
FOR SOME %ELEMENT。
Collection Index
FOR SOME %ELEMENT的一个重要用途是使用集合索引选择元素。
如果为字段定义了适当的KEYS或ELEMENTS索引， IRIS将使用该索引，而不是直接引用字段值元素。
如果定义了以下集合索引:
INDEX fcIDX1 ON FavoriteColors(ELEMENTS);
下面的查询使用了这个索引:
SELECT Name,FavoriteColors FROM Sample.Person
WHERE FOR SOME %ELEMENT(FavoriteColors) (%VALUE='Red')
如果定义了以下集合索引:
INDEX fcIDX2 ON FavoriteColors(KEYS) [ Type = bitmap ];
下面的查询使用了这个索引:
SELECT Name,FavoriteColors FROM Sample.Person
WHERE FOR SOME %ELEMENT(FavoriteColors) (%KEY=2)
示例
下面的示例使用FOR SOME %ELEMENT返回FavoriteColors列表中包含元素'Red'的那些行:
SELECT Name,FavoriteColors
FROM Sample.Person
WHERE FOR SOME %ELEMENT(FavoriteColors) (%VALUE='Red')
在下面的示例中，%VALUE谓词包含一个In语句，该语句指定一个用逗号分隔的列表。
这个例子返回FavoriteColors列表中包含元素'Red'或元素'Blue'(或两者都包含)的那些行:
SELECT Name,FavoriteColors
FROM Sample.Person
WHERE FOR SOME %ELEMENT(FavoriteColors) (%VALUE IN ('Red','Blue'))
下面的示例使用带有两个Contains操作符([)的谓词子句。
它返回那些FavoriteColors列表中包含包含小写'l'和小写'e'的元素的行(contains操作符是大小写敏感的)。
在本例中，元素“Blue”、“Yellow”和“Purple”:
SELECT Name,FavoriteColors AS Preferences
FROM Sample.Person
WHERE FOR SOME %ELEMENT(FavoriteColors) AS fc (fc.%VALUE [ 'l' AND fc.%VALUE [ 'e')
这个示例还演示了如何使用元素别名。
下面的动态SQL示例使用%KEY根据FavoriteColors中的元素数量返回行。
第一个%Execute()设置%KEY=1，返回所有包含一个或多个FavoriteColors元素的行。
第二个%Execute()设置%KEY=2，返回所有包含两个或更多favoritecolor元素的行:
ClassMethod ForSomeElement()
{
s q1 = "SELECT %ID,Name,FavoriteColors FROM Sample.Person "
s q2 = "WHERE FOR SOME %ELEMENT(FavoriteColors) (%KEY=?)"
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(1)
d rset.%Display()
w !,"End of data %KEY 1",!!
s rset = tStatement.%Execute(2)
d rset.%Display()
w !,"End of data %KEY 2"
}
