在本教程中,您将学习如何使用Oracle ALL运算符将值与列表或子查询进行比较。
Oracle ALL操作符简介
Oracle ALL操作符用于将值与子查询返回的值列表或结果集进行比较。
以下显示了与列表或子查询一起使用的ALL运算符的语法:
operator ALL ( v1, v2, v3)
operator ALL ( subquery)
在这个语法中,
ALL运算符前面必须有一个运算符,例如:=,!=,>,>=,
列表或子查询必须用圆括号包围。
使用ALL运算符将值与列表进行比较时,Oracle将初始条件扩展到列表的所有元素,并使用AND运算符将它们组合在一起,如下所示:
SELECT
*
FROM
table_name
WHERE
c > ALL (
v1,
v2,
v3
);
-- 以上语句转换为 ALL 运算符后
SELECT
*
FROM
table_name
WHERE
c > v1
AND c > v2
AND c > v3;
如果使用ALL运算符将值与子查询返回的结果集进行比较,则Oracle执行两步转换,如下所示:
SELECT product_name,
list_price
FROM products
WHERE list_price > ALL
( SELECT list_price
FROM products
WHERE category_id = 1 )
ORDER BY product_name;
-- 1st step: transformation that uses ANY
SELECT product_name,
list_price
FROM products p1
WHERE NOT( p1.list_price <= ANY
(SELECT list_price
FROM products p2
WHERE category_id = 1 ))
ORDER BY product_name;
-- 2nd step: transformation that eliminates ANY
SELECT product_name,
list_price
FROM products p1
WHERE NOT EXISTS
(SELECT p2.list_price
FROM products p2
WHERE p2.category_id = 1
AND p2.list_price >= p1.list_price )
ORDER BY product_name;
如果子查询不返回行,则以下条件的计算结果为true:
operator ALL (subquery)
这意味着在WHERE子句中使用上述条件的查询将返回所有行,以防子查询返回任何行。
SELECT
*
FROM
table_name
WHERE
col operator ALL(subquery);
Oracle ALL运算符示例
以下示例查找每个类别中产品的平均标价:
SELECT
ROUND( AVG( list_price ),2 ) avg_list_price
FROM
products
GROUP BY
category_id
ORDER BY
avg_list_price DESC;
执行上面示例代码,得到以下结果 -
1. col> ALL(list)
如果col大于列表中的最大值,则表达式的计算结果为true。
例如,以下查询查找标价大于平均价格列表最大价格的产品:
SELECT
product_name,
list_price
FROM
products
WHERE
list_price > ALL(
SELECT
AVG( list_price )
FROM
products
GROUP BY
category_id
)
ORDER BY
list_price ASC;
执行上面示例代码,得到以下结果 -
2. col < ALL(list)
如果col小于列表中的最小值,则表达式的计算结果为true。
SELECT
product_name,
list_price
FROM
products
WHERE
list_price < ALL(
SELECT
AVG( list_price )
FROM
products
GROUP BY
category_id
)
ORDER BY
list_price DESC;
例如,以下查询将查找标价低于平均价格列表中最小价格的产品:
3. col> = ALL(list)
如果col大于或等于列表中的最大值,则表达式的计算结果为true。
以下语句返回列表价格大于或等于2200的CPU产品:
SELECT
product_name,
list_price
FROM
products
WHERE
list_price >= ALL(
1000,
1500,
2200
)
AND category_id = 1
ORDER BY
list_price DESC;
执行上面示例代码,得到以下结果 -
4. col <= ALL(list)
如果col小于或等于列表中的最小值,则表达式的计算结果为true。
以下语句返回标价小于或等于列表中最小值的977.99的CPU产品。
SELECT
product_name,
list_price
FROM
products
WHERE
list_price <= ALL(
977.99,
1000,
2200
)
AND category_id = 1
ORDER BY
list_price DESC;
执行上面示例代码,得到以下结果 -
5. col = ALL ( list)
如果col匹配列表中的所有值,则表达式的计算结果为true。
6. col!= ALL(list)
如果col不匹配列表中的任何值,则表达式的计算结果为true。
在本教程中,您已经学习如何使用Oracle ALL操作符将值与列表或子查询进行比较。