SOME | ANY (Transact-SQL)SOME | ANY (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. SOME 和 ANY 是等效的。SOME and ANY are equivalent.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
scalar_expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }   
     { SOME | ANY } ( subquery )   

参数Arguments

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

{ = | <> | != | > | >= | !> | < | <= | !< }{ = | <> | != | > | >= | !> | < | <= | !< }
任何有效的比较运算符。Is any valid comparison operator.

SOME | ANYSOME | ANY
指定应进行比较。Specifies that a comparison should be made.

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

结果类型Result Types

BooleanBoolean

结果值Result Value

对于任何对 (scalar_expression,x)(其中 x 是单列集中的值),当指定的比较是 TRUE 时,SOME 或 ANY 返回 TRUE;否则返回 FALSE 。SOME or ANY returns TRUE when the comparison specified is TRUE for any pair (scalar_expression,x) where x is a value in the single-column set; otherwise, returns FALSE.

RemarksRemarks

SOME 要求 scalar_expression 与子查询返回的至少一个值比较时满足比较条件。SOME requires the scalar_expression to compare positively to at least one value returned by the subquery. 有关要求 scalar_expression 与子查询返回的每个值比较时都符合比较条件的语句,请参阅 ALL (Transact-SQL)For statements that require the scalar_expression to compare positively to every value that is returned by the subquery, see ALL (Transact-SQL). 例如,如果子查询返回的值为 2 和 3,则对于值为 2 的 scalar_express , scalar_expression = SOME(子查询)的计算结果为 TRUE。For instance, if the subquery returns values of 2 and 3, scalar_expression = SOME (subquery) would evaluate as TRUE for a scalar_express 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.

示例Examples

A.A. 运行简单示例Running a simple example

以下语句创建一个简单表,并向 1 列添加值 234IDThe following statements create a simple table and add the values of 1, 2, 3, and 4 to the ID column.

CREATE TABLE T1  
(ID int) ;  
GO  
INSERT T1 VALUES (1) ;  
INSERT T1 VALUES (2) ;  
INSERT T1 VALUES (3) ;  
INSERT T1 VALUES (4) ;  

以下查询返回 TRUE,因为 3 小于表中的某些值。The following query returns TRUE because 3 is less than some of the values in the table.

IF 3 < SOME (SELECT ID FROM T1)  
PRINT 'TRUE'   
ELSE  
PRINT 'FALSE' ;  

下面的查询返回 FALSE,因为 3 并不小于表中的所有值。The following query returns FALSE because 3 isn't less than all of the values in the table.

IF 3 < ALL (SELECT ID FROM T1)  
PRINT 'TRUE'   
ELSE  
PRINT 'FALSE' ;  

B.B. 运行实际示例Running a practical example

以下示例创建一个存储过程,该过程确定是否能够在指定的天数中制造出 AdventureWorks2012 数据库中具有指定 SalesOrderID 的所有组件。The following example creates a stored procedure that determines whether all the components of a specified SalesOrderID in the AdventureWorks2012 database can be manufactured in the specified number of days. 该示例使用子查询为具有特定 DaysToManufacture 的所有组件创建 SalesOrderID 值的列表,然后测试子查询返回的值中是否有大于指定天数的值。The example uses a subquery to create a list of the number of DaysToManufacture value for all the components of the specific SalesOrderID, and then tests whether any of the values that are returned by the subquery are greater than the number of days specified. 如果返回的所有 DaysToManufacture 的值都小于规定的天数,则条件为 TRUE,并输出第一个消息。If every value of DaysToManufacture that is returned is less than the number provided, the condition is TRUE and the first message is printed.

-- Uses AdventureWorks  
  
CREATE PROCEDURE ManyDaysToComplete @OrderID int, @NumberOfDays int  
AS  
IF   
@NumberOfDays < SOME  
   (  
    SELECT DaysToManufacture  
    FROM Sales.SalesOrderDetail  
    JOIN Production.Product   
    ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID   
    WHERE SalesOrderID = @OrderID  
   )  
PRINT 'At least one item for this order can't be manufactured in specified number of days.'  
ELSE   
PRINT 'All items for this order can be manufactured in the 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 that requires 2 days and two components that require 0 days. 第一个语句符合条件。The first statement meets the criteria. 第二个查询不符合条件。The second query doesn't.

EXECUTE ManyDaysToComplete 49080, 2 ;  

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

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

EXECUTE ManyDaysToComplete 49080, 1 ;  

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

At least one item for this order can't be manufactured in specified number of days.

另请参阅See Also

ALL (Transact-SQL) ALL (Transact-SQL)
CASE (Transact-SQL) CASE (Transact-SQL)
内置函数 (Transact-SQL) Built-in Functions (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)