量化数据 sql
您是否想过SQL的ANY
( 也: SOME
)和ALL
关键字背后的用例?
您可能尚未在野外遇到这些关键字。 但是它们可能非常有用。 但是首先,让我们看看如何在SQL标准中对其进行定义。 最简单的部分:
8.7 <quantified comparison predicate>
Function
Specify a quantified comparison.
Format
<quantified comparison predicate> ::=
<row value constructor> <comp op>
<quantifier> <table subquery>
<quantifier> ::= <all> | <some>
<all> ::= ALL
<some> ::= SOME | ANY
直观地,这样的量化比较谓词可以这样使用:
-- Is any person of age 42?
42 = ANY (SELECT age FROM person)
-- Are all persons younger than 42?
42 > ALL (SELECT age FROM person)
让我们保持有用的。 请注意,您可能已经使用不同的语法编写了上述查询,例如:
-- Is any person of age 42?
42 IN (SELECT age FROM person)
-- Are all persons younger than 42?
42 > (SELECT MAX(age) FROM person)
实际上,您已经将<in predicate>
或大于谓词与<scalar subquery>
和聚合函数一起使用。
IN谓词
您可能使用了<in predicate>
就像上面使用ANY
<quantified comparison predicate>
并不是巧合。 实际上, <in predicate>
的指定方式如下:
8.4 <in predicate>
Syntax Rules
2) Let RVC be the <row value constructor> and let IPV
be the <in predicate value>.
3) The expression
RVC NOT IN IPV
is equivalent to
NOT ( RVC IN IPV )
4) The expression
RVC IN IPV
is equivalent to
RVC = ANY IPV
恰恰! SQL不漂亮吗? 请注意, 3)
的隐式结果导致NOT IN
谓词相对于NULL
行为非常特殊 ,很少有开发人员知道。
现在,它变得很棒
到目前为止,这些<quantified comparison predicate>
并没有什么不寻常的。 前面的所有示例都可以用“更惯用的”或“更日常”SQL进行模拟。
但是,仅当将<row value expression>
大于1的<row value expression>
与以下内容结合使用时, <quantified comparison predicate>
真棒才出现:
-- Is any person called "John" of age 42?
(42, 'John') = ANY (SELECT age, first_name FROM person)
-- Are all persons younger than 55?
-- Or if they're 55, do they all earn less than 150'000.00?
(55, 150000.00) > ALL (SELECT age, wage FROM person)
请参阅此SQLFiddle中有关PostgreSQL上述查询 。
在这一点上,值得一提的是,实际上很少有数据库支持……
- 行值表达式,或…
- 具有行值表达式的量化比较谓词
即使在SQL-92中指定,看起来22年后大多数数据库仍需要花费时间来实现此功能。
用jOOQ模拟这些谓词
但幸运的是,有jOOQ可以为您模拟这些功能。 即使您在项目中未使用jOOQ,如果要表达上述谓词,下面SQL转换步骤也会很有用。 让我们看一下如何在MySQL中完成此操作:
-- This predicate
(42, 'John') = ANY (SELECT age, first_name FROM person)
-- ... is the same as this:
EXISTS (
SELECT 1 FROM person
WHERE age = 42 AND first_name = 'John'
)
那其他谓词呢?
-- This predicate
(55, 150000.00) > ALL (SELECT age, wage FROM person)
-- ... is the same as these:
----------------------------
-- No quantified comparison predicate with
-- Row value expressions available
(55, 150000.00) > (
SELECT age, wage FROM person
ORDER BY 1 DESC, 2 DESC
LIMIT 1
)
-- No row value expressions available at all
NOT EXISTS (
SELECT 1 FROM person
WHERE (55 < age)
OR (55 = age AND 150000.00 <= wage)
)
显然, EXISTS
谓词几乎可以在每个数据库中使用,以模仿我们之前看到的内容。 如果您只需要一次仿真,以上示例就足够了。 但是,如果要更正式地使用<row value expression>
和<quantified comparison predicate>
,则最好正确进行SQL转换。
量化数据 sql