逻辑函数 - CHOOSE (Transact-SQL)Logical Functions - CHOOSE (Transact-SQL)

适用于:Applies to: 是 SQL ServerSQL Server (所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是的Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是 SQL ServerSQL Server (所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是的Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

SQL ServerSQL Server 中从值列表返回指定索引处的项。Returns the item at the specified index from a list of values in SQL ServerSQL Server.

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

语法Syntax

  
CHOOSE ( index, val_1, val_2 [, val_n ] )  

参数Arguments

索引 index
一个整数表达式,表示其后的项列表的从 1 开始的索引。Is an integer expression that represents a 1-based index into the list of the items following it.

如果提供的索引值具有 int 之外的数值数据类型,则该值将隐式转换为整数 。If the provided index value has a numeric data type other than int, then the value is implicitly converted to an integer. 如果索引值超出了值数组的界限,则 CHOOSE 返回 Null。If the index value exceeds the bounds of the array of values, then CHOOSE returns null.

val_1 … val_n val_1 ... val_n
任何数据类型的逗号分隔的值列表。List of comma separated values of any data type.

返回类型Return Types

从传递到函数的类型集中返回优先级最高的数据类型。Returns the data type with the highest precedence from the set of types passed to the function. 有关详细信息,请参阅数据类型优先级 (Transact-SQL)For more information, see Data Type Precedence (Transact-SQL).

备注Remarks

CHOOSE 像索引一样进入数组中,其中,数组由跟在索引参数之后的各参数组成。CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. 索引参数确定将返回以下哪些值。The index argument determines which of the following values will be returned.

示例Examples

A.A. 简单的 CHOOSE 示例Simple CHOOSE example

下面的示例从所提供的值列表中返回第三项。The following example returns the third item from the list of values that is provided.

SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;  

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

Result  
-------------  
Developer  
  
(1 row(s) affected)  

B.B. 基于列的简单 CHOOSE 示例Simple CHOOSE example based on column

以下示例基于 ProductCategoryID 列中的值返回简单字符串。The following example returns a simple character string based on the value in the ProductCategoryID column.

USE AdventureWorks2012;  
GO  
SELECT ProductCategoryID, CHOOSE (ProductCategoryID, 'A','B','C','D','E') AS Expression1  
FROM Production.ProductCategory;  
  

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

ProductCategoryID Expression1  
----------------- -----------  
3                 C  
1                 A  
2                 B  
4                 D  
  
(4 row(s) affected)  
  

C.C. 配合使用 CHOOSE 和 MONTHCHOOSE in combination with MONTH

以下示例返回雇佣员工时的季度。The following example returns the season in which an employee was hired. MONTH 函数用于从 HireDate 列返回月份值。The MONTH function is used to return the month value from the column HireDate.

USE AdventureWorks2012;  
GO  
SELECT JobTitle, HireDate, CHOOSE(MONTH(HireDate),'Winter','Winter', 'Spring','Spring','Spring','Summer','Summer',   
                                                  'Summer','Autumn','Autumn','Autumn','Winter') AS Quarter_Hired  
FROM HumanResources.Employee  
WHERE  YEAR(HireDate) > 2005  
ORDER BY YEAR(HireDate);  
  

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

JobTitle                                           HireDate   Quarter_Hired  
-------------------------------------------------- ---------- -------------  
Sales Representative                               2006-11-01 Autumn  
European Sales Manager                             2006-05-18 Spring  
Sales Representative                               2006-07-01 Summer  
Sales Representative                               2006-07-01 Summer  
Sales Representative                               2007-07-01 Summer  
Pacific Sales Manager                              2007-04-15 Spring  
Sales Representative                               2007-07-01 Summer  
  

另请参阅See Also

IIF (Transact-SQL)IIF (Transact-SQL)