ALL (Transact-SQL)ALL (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

比较标量值和单列集中的值。Compares a scalar value with a single-column set of values.

文章链接图标 Transact-SQL 语法约定Article link icon Transact-SQL Syntax Conventions

语法Syntax

  
scalar_expression { = | <> | != | > | >= | !> | < | <= | !< } ALL ( subquery )  

参数Arguments

scalar_expressionscalar_expression
为任意有效的表达式Is any valid expression.

{ = | <> | != | > | >= | !> | < | <= | !< }{ = | <> | != | > | >= | !> | < | <= | !< }
一个比较运算符。Is a comparison operator.

subquery subquery
返回单列结果集的子查询。Is a subquery that returns a result set of one column. 返回列的数据类型必须与 scalar_expression 的数据类型相同 。The data type of the returned column must be the same data type as the data type of scalar_expression.

受限的 SELECT 语句,其中不允许使用 ORDER BY 子句和 INTO 关键字。Is a restricted SELECT statement, in which the ORDER BY clause and the INTO keyword aren't allowed.

结果类型Result Types

BooleanBoolean

结果值Result Value

如果指定的比较对于所有比较对 (scalar_expression , x) 均为 TRUE(其中 x 是单列集中的值),则返回 TRUE。Returns TRUE when the comparison specified is TRUE for all pairs (scalar_expression,x), when x is a value in the single-column set. 否则返回 FALSE。Otherwise returns FALSE.

RemarksRemarks

ALL 要求 scalar_expression 与子查询返回的每个值进行比较时都应满足比较条件 。ALL requires the scalar_expression to compare positively to every value that is returned by the subquery. 例如,如果子查询返回的值为 2 和 3,则对于值为 2 的 scalar_expression,scalar_expression <= ALL(子查询)的计算结果为 TRUE 。For instance, if the subquery returns values of 2 and 3, scalar_expression <= ALL (subquery) would evaluate as TRUE for a scalar_expression of 2. 如果子查询返回值 2 和 3,scalar_expression = ALL(子查询)的计算结果为 FALSE,因为子查询的某些值(值 3)不符合表达式的条件。If the subquery returns values of 2 and 3, scalar_expression = ALL (subquery) would evaluate as FALSE, because some of the values of the subquery (the value of 3) wouldn't meet the criteria of the expression.

有关要求 scalar_expression 只与子查询返回的某一个值比较时满足比较条件的语句,请参阅 SOME | ANY (Transact-SQL)For statements that require the scalar_expression to compare positively to only one value that is returned by the subquery, see SOME | ANY (Transact-SQL).

本文讨论了 ALL 用于子查询的情况。This article refers to ALL when it is used with a subquery. ALL 也可以与 UNIONSELECT 一起使用。ALL can also be used with UNION and SELECT.

示例Examples

以下示例创建一个存储过程,该过程确定是否能够在指定的天数中制造出 AdventureWorks2012AdventureWorks2012 数据库中具有指定 SalesOrderID 的所有组件。The following example creates a stored procedure that determines whether all the components of a specified SalesOrderID in the AdventureWorks2012AdventureWorks2012 database can be manufactured in the specified number of days. 该示例使用子查询为具有特定 DaysToManufacture 的所有组件创建 SalesOrderID 值的列表,然后确认所有 DaysToManufacture 都在指定的天数内。The example uses a subquery to create a list of the number of DaysToManufacture values for all of the components of the specific SalesOrderID, and then confirms that all the DaysToManufacture are within the number of days specified.

-- Uses AdventureWorks  
  
CREATE PROCEDURE DaysToBuild @OrderID int, @NumberOfDays int  
AS  
IF   
@NumberOfDays >= ALL  
   (  
    SELECT DaysToManufacture  
    FROM Sales.SalesOrderDetail  
    JOIN Production.Product   
    ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID   
    WHERE SalesOrderID = @OrderID  
   )  
PRINT 'All items for this order can be manufactured in specified number of days or less.'  
ELSE   
PRINT 'Some items for this order can''t be manufactured in specified number of days or less.' ;  
  

若要测试该过程,请使用 SalesOrderID 49080(具有一个需要 2 天的组件和两个需要 0 天的组件)来执行该过程。To test the procedure, execute the procedure by using the SalesOrderID 49080, which has one component requiring 2 days and two components that require 0 days. 下面的第一个语句符合条件。The first statement below meets the criteria. 第二个查询不符合条件。The second query doesn't.

EXECUTE DaysToBuild 49080, 2 ;  

下面是结果集:Here is the result set.

All items for this order can be manufactured in specified number of days or less.

EXECUTE DaysToBuild 49080, 1 ;  

下面是结果集:Here is the result set.

Some items for this order can't be manufactured in specified number of days or less.

另请参阅See Also

CASE (Transact-SQL) CASE (Transact-SQL)
表达式 (Transact-SQL) Expressions (Transact-SQL)
内置函数 (Transact-SQL) Built-in Functions (Transact-SQL)
LIKE (Transact-SQL) LIKE (Transact-SQL)
运算符 (Transact-SQL) Operators (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
WHERE (Transact-SQL) WHERE (Transact-SQL)
IN (Transact-SQL)IN (Transact-SQL)