PostgreSQL - ALL 运算符

PostgreSQL ALL 运算符

PostgreSQL ALL 运算符是一个强大的比较工具，它允许您将单个值与列表或子查询结果中的每个值进行比较。如果您的比较对集合中的所有元素都成立，则返回 true；否则，返回 false。

语法

ALL 运算符在 WHERE 或 HAVING 子句中使用：

expression operator ALL (subquery | ARRAY[...])

其中：

expression 是您要测试的值。
operator 是一个标准比较运算符，例如 =、 <>、>、 <、 >= 或 <=。
该集合可以是返回单列的 subquery（子查询），也可以是显式的 ARRAY（数组）构造器。

实际示例：候选人技能评估

设想一个招聘场景，我们需要找到在多个评估分数上都符合严格标准的候选人。ALL 运算符非常适合这种情况。

首先，让我们创建并填充 candidates 表。

DROP TABLE IF EXISTS candidates;
CREATE TABLE candidates (
    id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    experience_years INT,
    assessment_scores INT[] -- 一个整数分数数组
);

INSERT INTO candidates (full_name, experience_years, assessment_scores) VALUES
('Alice Johnson', 5, ARRAY[85, 90, 95]),
('Bob Smith', 7, ARRAY[88, 92, 99]),
('Charlie Brown', 2, ARRAY[60, 75, 80]),
('Diana Prince', 6, ARRAY[70, 85, 88]),
('Edward King', 4, ARRAY[55, 65, 70]),
('Fiona Glenanne', 10, ARRAY[95, 98, 100]);

查询 1：查找所有分数都高于 80 的候选人

我们希望找到所有评估分数都大于 80 的精英候选人。查询 80 < ALL(assessment_scores) 转换为：“80 小于第一个分数 AND 80 小于第二个分数 AND…”。

SELECT full_name, assessment_scores
FROM candidates
WHERE 80 < ALL(assessment_scores);

此查询产生以下结果：

   full_name    | assessment_scores
----------------+-------------------
 Alice Johnson  | {85,90,95}
 Bob Smith      | {88,92,99}
 Fiona Glenanne | {95,98,100}
(3 rows)

查询 2：结合子查询使用 ALL

ALL 更常见的用例是与子查询一起使用。让我们创建另一个表 products 来演示这一点。我们想找到“高级”类别中所有价格比“标准”类别中所有产品都贵的产品。

DROP TABLE IF EXISTS products;
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

INSERT INTO products (product_name, category, price) VALUES
('Standard Keyboard', 'Standard', 25.00),
('Standard Mouse', 'Standard', 15.00),
('Premium Keyboard', 'Premium', 90.00),
('Premium Mouse', 'Premium', 75.00),
('Ultra-Premium Keyboard', 'Premium', 120.00);

子查询 (SELECT price FROM products WHERE category = 'Standard') 返回集合 {25.00, 15.00}。外部查询检查哪些“高级”产品的价格大于这两个值。

SELECT product_name, price
FROM products
WHERE category = 'Premium'
  AND price > ALL (SELECT price FROM products WHERE category = 'Standard');

结果：

      product_name      | price
------------------------+--------
 Premium Keyboard       |  90.00
 Premium Mouse          |  75.00
 Ultra-Premium Keyboard | 120.00
(3 rows)

与其他运算符的比较

ALL 与 ANY/SOME：如果比较对集合中至少一个值成立，则 ANY 返回 true。ALL 要求所有值都满足条件。> ANY 表示“大于最小值”，而 > ALL 表示“大于最大值”。
ALL 与 IN：expression = ANY(set) 等价于 expression IN (set)。但是，expression <> ALL(set) 与 expression NOT IN (set) 不同。前者表示表达式不等于集合中的任何值，而后者检查非成员关系。

ALL 运算符是一个特定且强大的工具，用于对一组值进行通用比较，这对于强制执行严格、全面的规则的查询来说是无价的。