SELECT - FOR 子句 (Transact-SQL)SELECT - FOR Clause (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

使用 FOR 子句指定查询结果的以下查询结果之一。Use the FOR clause to specify one of the following options for query results.

  • 允许在查看浏览模式游标中的查询结果时通过指定 FOR BROWSE 来更新。Allow updates while viewing query results in a browse mode cursor by specifying FOR BROWSE.

  • 通过指定 FOR XML,将查询结果格式化为 XML。Format query results as XML by specifying FOR XML.

  • 通过指定 FOR JSON,将查询结果格式化为 JSON 。Format query results as JSON by specifying FOR JSON.

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

语法Syntax

[ FOR { BROWSE | <XML> | <JSON>} ]  
  
<XML> ::=  
XML   
{   
    { RAW [ ( 'ElementName' ) ] | AUTO }   
    [   
        <CommonDirectivesForXML>   
        [ , { XMLDATA | XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] } ]   
        [ , ELEMENTS [ XSINIL | ABSENT ]   
    ]  
  | EXPLICIT   
    [   
        <CommonDirectivesForXML>   
        [ , XMLDATA ]   
    ]  
  | PATH [ ( 'ElementName' ) ]   
    [  
        <CommonDirectivesForXML>   
        [ , ELEMENTS [ XSINIL | ABSENT ] ]  
    ]  
}   
  
<CommonDirectivesForXML> ::=   
[ , BINARY BASE64 ]  
[ , TYPE ]  
[ , ROOT [ ( 'RootName' ) ] ]  
  
<JSON> ::=  
JSON   
{   
    { AUTO | PATH }   
    [   
        [ , ROOT [ ( 'RootName' ) ] ]  
        [ , INCLUDE_NULL_VALUES ]  
        [ , WITHOUT_ARRAY_WRAPPER ]  
    ]  
  
}

FOR BROWSEFOR BROWSE

BROWSEBROWSE
指定可以在查看 DB-Library 浏览模式游标中的数据时进行更新。Specifies that updates be allowed while viewing the data in a DB-Library browse mode cursor. 如果表包括 timestamp 列,并且表有唯一索引,而且在发送到 SQL ServerSQL Server 实例的 SELECT 语句中 FOR BROWSE 选项位于末尾,则可以在应用程序中浏览该表。A table can be browsed in an application if the table includes a timestamp column, the table has a unique index, and the FOR BROWSE option is at the end of the SELECT statements sent to an instance of SQL ServerSQL Server.

备注

不能在包括 FOR BROWSE 选项的 SELECT 语句中使用 <lock_hint> HOLDLOCK。You cannot use the <lock_hint> HOLDLOCK in a SELECT statement that includes the FOR BROWSE option.

FOR BROWSE 不能出现在由 UNION 运算符所联接的 SELECT 语句中。FOR BROWSE cannot appear in SELECT statements that are joined by the UNION operator.

备注

如果表的唯一索引键列可为空,并且表在外部联接的内侧,则浏览模式不支持索引。When the unique index key columns of a table are nullable, and the table is on the inner side of an outer join, the index is not supported by browse mode.

使用浏览模式可以扫描 SQL ServerSQL Server 表中的行并逐行更新表中的数据。The browse mode lets you scan the rows in your SQL ServerSQL Server table and update the data in your table one row at a time. 若要以浏览模式访问应用程序中的 SQL ServerSQL Server 表,必须使用下列两个选项之一:To access a SQL ServerSQL Server table in your application in the browse mode, you must use one of the following two options:

  • 用于访问 SQL ServerSQL Server 表的数据的 SELECT 语句必须以 FOR BROWSE 关键字结尾。The SELECT statement that you use to access the data from your SQL ServerSQL Server table must end with the keywords FOR BROWSE. 启用 FOR BROWSE 选项以使用浏览模式时,将创建时态表。When you turn on the FOR BROWSE option to use browse mode, temporary tables are created.

  • 必须运行以下 Transact-SQLTransact-SQL 语句,以使用 NO_BROWSETABLE 选项打开浏览模式:You must run the following Transact-SQLTransact-SQL statement to turn on the browse mode by using the NO_BROWSETABLE option:

    SET NO_BROWSETABLE ON  
    

    启用 NO_BROWSETABLE 选项时,所有 SELECT 语句的行为就像在这些语句后追加了 FOR BROWSE 选项一样 。When you turn on the NO_BROWSETABLE option, all the SELECT statements behave as if the FOR BROWSE option is appended to the statements. 但是,NO_BROWSETABLE 选项不会创建 FOR BROWSE 选项通常用于向应用程序发送结果的时态表 。However, the NO_BROWSETABLE option does not create the temporary tables that the FOR BROWSE option generally uses to send the results to your application.

当使用涉及外部联接语句的 SELECT 查询尝试以浏览模式访问 SQL ServerSQL Server 表中的数据,并且当对外部联接语句内侧已存在的表定义唯一索引时,浏览模式不支持此唯一索引。When you try to access the data from SQL ServerSQL Server tables in browse mode by using a SELECT query that involves an outer join statement, and when a unique index is defined on the table that is present on the inner side of an outer join statement, the browse mode does not support the unique index. 仅当所有唯一索引键列可接受 Null 值时,浏览模式才支持唯一索引。The browse mode supports the unique index only when all the unique index key columns can accept null values. 如果满足下列条件,浏览模式不支持唯一索引:The browse mode does not support the unique index if the following conditions are true:

  • 尝试使用包含外部联接语句的 SELECT 查询在浏览模式中访问 SQL ServerSQL Server 表的数据。You try to access the data from SQL ServerSQL Server tables in browse mode by using a SELECT query that involves an outer join statement.

  • 对外部联接语句内侧已存在的表定义唯一索引。A unique index is defined on the table that is present on the inner side of an outer join statement.

若要在浏览模式下重现此行为,请按下列步骤操作:To reproduce this behavior in the browse mode, follow these steps:

  1. SQL Server Management StudioSQL Server Management Studio 中,创建一个名为 SampleDB 的数据库。In SQL Server Management StudioSQL Server Management Studio, create a database, named SampleDB.

  2. 在 SampleDB 数据库中,创建均包含名为 c1 的列的 tleft 表和 tright 表。In the SampleDB database, create a tleft table and a tright table that both contain a single column that is named c1. 对 tleft 表中的 c1 列定义唯一索引,并将此列设置为接受 Null 值。Define a unique index on the c1 column in the tleft table, and set the column to accept null values. 为此,请在相应的查询窗口中运行下列 Transact-SQLTransact-SQL 语句:To do this, run the following Transact-SQLTransact-SQL statements in an appropriate query window:

    CREATE TABLE tleft(c1 INT NULL UNIQUE) ;  
    GO   
    CREATE TABLE tright(c1 INT NULL) ;  
    GO  
    
  3. 在 tleft 表和 tright 表中插入多个值。Insert several values in the tleft table and the tright table. 请确保在 tleft 表中插入一个 Null 值。Make sure that you insert a null value in the tleft table. 为此,请在查询窗口中运行下列 Transact-SQLTransact-SQL 语句:To do this, run the following Transact-SQLTransact-SQL statements in the query window:

    INSERT INTO tleft VALUES(2) ;  
    INSERT INTO tleft VALUES(NULL) ;  
    INSERT INTO tright VALUES(1) ;  
    INSERT INTO tright VALUES(3) ;  
    INSERT INTO tright VALUES(NULL) ;  
    GO  
    
  4. 开启 NO_BROWSETABLE 选项。Turn on the NO_BROWSETABLE option. 为此,请在查询窗口中运行下列 Transact-SQLTransact-SQL 语句:To do this, run the following Transact-SQLTransact-SQL statements in the query window:

    SET NO_BROWSETABLE ON ;  
    GO  
    
  5. 通过在 SELECT 查询中使用外部联接语句来访问 tleft 表和 tright 表中的数据。Access the data in the tleft table and the tright table by using an outer join statement in the SELECT query. 请确保 tleft 表位于外部联接语句的内侧。Make sure that the tleft table is on the inner side of the outer join statement. 为此,请在查询窗口中运行下列 Transact-SQLTransact-SQL 语句:To do this, run the following Transact-SQLTransact-SQL statements in the query window:

    SELECT tleft.c1   
    FROM tleft   
    RIGHT JOIN tright   
    ON tleft.c1 = tright.c1   
    WHERE tright.c1 <> 2 ;
    

    请注意“结果”窗格中的以下输出:Notice the following output in the Results pane:

    c1c1

    ----

    NullNULL

    NullNULL

在运行 SELECT 查询以便以浏览模式访问表之后,对于 tleft 表中的 c1 列,SELECT 查询的结果集包含两个 Null 值,这是因为在右外部联接语句中定义了该列。After you run the SELECT query to access the tables in the browse mode, the result set of the SELECT query contains two null values for the c1 column in the tleft table because of the definition of the right outer join statement. 因此,您无法在结果集中区分源自该表的 Null 值和右外部联接语句引入的 Null 值。Therefore, in the result set, you cannot distinguish between the null values that came from the table and the null values that the right outer join statement introduced. 如果必须忽略结果集的 Null 值,则可能收到错误结果。You might receive incorrect results if you must ignore the null values from the result set.

备注

如果唯一索引中包含的列不接受 Null 值,则结果集中的所有 Null 值都是由右外部联接语句引入的。If the columns that are included in the unique index do not accept null values, all the null values in the result set were introduced by the right outer join statement.

FOR XMLFOR XML

XMLXML
指定以 XML 文档返回查询的结果。Specifies that the results of a query are to be returned as an XML document. 必须指定下列 XML 模式之一:RAW、AUTO、EXPLICIT。One of the following XML modes must be specified: RAW, AUTO, EXPLICIT. 有关 XML 数据和 SQL ServerSQL Server 的详细信息,请参阅 FOR XML (SQL Server)For more information about XML data and SQL ServerSQL Server, see FOR XML (SQL Server).

RAW [ ('ElementName') ]RAW [ ('ElementName') ]
获得查询结果并将结果集内的每一行转换为以一般标识符 <row /> 作为元素标记的 XML 元素。Takes the query result and transforms each row in the result set into an XML element with a generic identifier <row /> as the element tag. (可选)可以为该行元素指定名称。You can optionally specify a name for the row element. 产生的 XML 输出将指定的 ElementName 用作每行生成的行元素。The resulting XML output uses the specified ElementName as the row element generated for each row. 有关详细信息,请参阅 将 RAW 模式与 FOR XML 一起使用For more information, see Use RAW Mode with FOR XML.

AUTOAUTO
以简单的嵌套 XML 树返回查询结果。Returns query results in a simple, nested XML tree. FROM 子句中每个在 SELECT 子句中至少列出一次的表都被表示为一个 XML 元素。Each table in the FROM clause, for which at least one column is listed in the SELECT clause, is represented as an XML element. SELECT 子句中列出的列映射到适当的元素属性。The columns listed in the SELECT clause are mapped to the appropriate element attributes. 有关详细信息,请参阅 将 AUTO 模式与 FOR XML 一起使用For more information, see Use AUTO Mode with FOR XML.

EXPLICITEXPLICIT
指定显式定义产生的 XML 树的形状。Specifies that the shape of the resulting XML tree is defined explicitly. 使用该模式要求必须以一种特定的方式编写查询,即显式指定与想要的嵌套有关的其他信息。Using this mode, queries must be written in a particular way so that additional information about the desired nesting is specified explicitly. 有关详细信息,请参阅 将 EXPLICIT 模式与 FOR XML 一起使用For more information, see Use EXPLICIT Mode with FOR XML.

XMLDATAXMLDATA
返回内联 XDR 架构,但不将根元素添加到结果中。Returns inline XDR schema, but does not add the root element to the result. 如果指定了 XMLDATA,则 XDR 架构将被追加到文档末尾。If XMLDATA is specified, XDR schema is appended to the document.

重要

XMLDATA 指令已弃用。The XMLDATA directive is deprecated. 如果是 RAW 和 AUTO 模式,请使用 XSD 生成。Use XSD generation in the case of RAW and AUTO modes. 在 EXPLICIT 模式下,没有 XMLDATA 指令的替代项。There is no replacement for the XMLDATA directive in EXPLICIT mode. 此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

禁止显示不需要的换行符:可以使用 SQL Server Management Studio (SSMS) 来发出使用 FOR XML 子句的查询。Suppress unwanted line breaks: You might use SQL Server Management Studio (SSMS) to issue a query that uses the FOR XML clause. 有时会返回大量 XML 并在一个网格单元格中显示。Sometimes a large amount of XML is returned and displayed in one grid cell. XML 字符串可能比 SSMS 网格单元在单个行上可以容纳的长度更长。The XML string could be longer than one SSMS grid cell can hold on a single line. 在这些情况下,SSMS 可能会在整个 XML 字符串的长段之间插入换行符。In these cases, SSMS might insert line break characters between long segments of the whole XML string. 此类换行可能发生在子某个字符串的中间,不应跨行拆分该子字符串。Such line breaks might occur in the middle of a substring that should not be split across lines. 可以使用强制转换 AS XMLDATA 来阻止换行符。You can prevent the line breaks by using a cast AS XMLDATA. 使用 FOR JSON PATH 时,此解决方案也适用。This solution can also apply when you use FOR JSON PATH. 该技巧在 Stack Overflow 上进行了介绍,并显示在以下 Transact-SQL 示例 SELECT 语句中:The technique is discussed on Stack Overflow, and is shown in the following Transact-SQL sample SELECT statement:

XMLSCHEMA [ ('TargetNameSpaceURI') ]XMLSCHEMA [ ('TargetNameSpaceURI') ]
返回内联 XSD 架构。Returns inline XSD schema. 如果指定该指令(用于返回架构中指定的命名空间),则可以选择指定目标命名空间 URI。You can optionally specify a target namespace URI when you specify this directive, which returns the specified namespace in the schema. 有关详细信息,请参阅 生成内联 XSD 架构For more information, see Generate an Inline XSD Schema.

ELEMENTSELEMENTS
指定列作为子元素返回。Specifies that the columns are returned as subelements. 否则,列将映射到 XML 属性。Otherwise, they are mapped to XML attributes. 只在 RAW、AUTO 和 PATH 模式中支持该选项。This option is supported in RAW, AUTO and PATH modes only. 有关详细信息,请参阅 将 RAW 模式与 FOR XML 一起使用For more information, see Use RAW Mode with FOR XML.

XSINILXSINIL
指定为 NULL 列值创建其 xsi:nil 属性设置为 True 的元素 。Specifies that an element with xsi:nil attribute set to True be created for NULL column values. 该选项只能与 ELEMENTS 指令一起指定。This option can only be specified with ELEMENTS directive. 有关详细信息,请参阅:For more information, see:

ABSENTABSENT
指示对于空列值,将不在 XML 结果中添加对应的 XML 元素。Indicates that for null column values, corresponding XML elements will not be added in the XML result. 该选项只能与 ELEMENTS 一起指定。Specify this option only with ELEMENTS.

PATH [ ('ElementName') ]PATH [ ('ElementName') ]
为结果集中的每一行生成 <row> 元素包装器。Generates a <row> element wrapper for each row in the result set. 可以根据需要为 <row> 元素包装器指定元素名称。You can optionally specify an element name for the <row> element wrapper. 如果提供了空字符串(例如 FOR XML PATH ('')),则不生成包装器元素。If an empty string is provided, such as FOR XML PATH ('') ), a wrapper element is not generated. 使用 PATH 可能为使用 EXPLICIT 指令所编写的查询提供更简单的代替方案。Using PATH may provide a simpler alternative to queries written using the EXPLICIT directive. 有关详细信息,请参阅 将 PATH 模式与 FOR XML 一起使用For more information, see Use PATH Mode with FOR XML.

BINARY BASE64BINARY BASE64
指定查询返回二进制 base64 编码格式的二进制数据。Specifies that the query returns the binary data in binary base64-encoded format. 使用 RAW 和 EXPLICIT 模式检索二进制数据时,必须指定该选项。When you retrieve binary data by using RAW and EXPLICIT mode, this option must be specified. 这是 AUTO 模式中的默认值。This is the default in AUTO mode.

TYPETYPE
指定查询以 xml 类型返回结果。Specifies that the query returns results as xml type. 有关详细信息,请参阅 TYPE Directive in FOR XML QueriesFor more information, see TYPE Directive in FOR XML Queries.

ROOT [ ('RootName') ]ROOT [ ('RootName') ]
指定将一个顶级元素添加到结果 XML 中。Specifies that a single top-level element be added to the resulting XML. 可以选择指定要生成的根元素名称。You can optionally specify the root element name to generate. 如果不指定可选的根名称,则添加默认的 <root> 元素。If the optional root name is not specified, the default <root> element is added.

有关详细信息,请参阅 FOR XML (SQL Server)For more info, see FOR XML (SQL Server).

FOR XML 示例FOR XML Example

以下示例指定具有 FOR XML AUTOTYPE 选项的 XMLSCHEMAThe following example specifies FOR XML AUTO with the TYPE and XMLSCHEMA options. 由于有 TYPE 选项,因此结果集将作为 xml 类型返回到客户端。Because of the TYPE option, the result set is returned to the client as an xml type. XMLSCHEMA 选项指定在所返回的 XML 数据中包括内联 XSD 架构,而 ELEMENTS 选项指定 XML 结果是以元素为中心的。The XMLSCHEMA option specifies that the inline XSD schema is included in the XML data returned, and the ELEMENTS option specifies that the XML result is element-centric.

USE AdventureWorks2012;  
GO  
SELECT p.BusinessEntityID, FirstName, LastName, PhoneNumber AS Phone  
FROM Person.Person AS p  
JOIN Person.PersonPhone AS pph ON p.BusinessEntityID  = pph.BusinessEntityID  
WHERE LastName LIKE 'G%'  
ORDER BY LastName, FirstName   
FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS XSINIL;  

对于 JSONFOR JSON

JSONJSON
指定 FOR JSON,返回已格式化为 JSON 文本的查询结果。Specify FOR JSON to return the results of a query formatted as JSON text. 还必须指定以下某项 JSON 模式:AUTO 或 PATH。You also have to specify one of the following JSON modes : AUTO or PATH. 有关 FOR JSON 子句的详细信息,请参阅借助 FOR JSON 将查询结果格式化为 JSON (SQL Server)For more information about the FOR JSON clause, see Format Query Results as JSON with FOR JSON (SQL Server).

AUTOAUTO
根据 SELECT 语句的结构Format the JSON output automatically based on the structure of the SELECT statement
通过指定 FOR JSON AUTO 自动格式化 JSON 输出。by specifying FOR JSON AUTO. 有关详细信息和示例,请参阅在 AUTO 模式下自动格式化 JSON 输出 (SQL Server)For more info and examples, see Format JSON Output Automatically with AUTO Mode (SQL Server).

PATHPATH
通过指定Get full control over the format of the JSON output by specifying
FOR JSON PATH 获取对 JSON 输出格式的完全控制。FOR JSON PATH. 借助PATH 模式,你可以创建包装器对象,并嵌套复杂属性。PATH mode lets you create wrapper objects and nest complex properties. 有关详细信息和示例,请参阅在 PATH 模式下设置嵌套的 JSON 输出格式 (SQL Server)For more info and examples, see Format Nested JSON Output with PATH Mode (SQL Server).

INCLUDE_NULL_VALUESINCLUDE_NULL_VALUES
通过使用 FOR JSON 子句指定 INCLUDE_NULL_VALUES 选项,在 JSON 输出中包括 NULL 值 。Include null values in the JSON output by specifying the INCLUDE_NULL_VALUES option with the FOR JSON clause. 如果没有指定此选项,输出不会在查询结果中包括 NULL 值的 JSON 属性。If you don't specify this option, the output does not include JSON properties for null values in the query results. 有关详细信息和示例,请参阅使用 INCLUDE_NULL_VALUES 选项将 NULL 值包含在 JSON 输出中 (SQL Server)For more info and examples, see Include Null Values in JSON Output with the INCLUDE_NULL_VALUES Option (SQL Server).

ROOT [ ('RootName') ]ROOT [ ('RootName') ]
通过使用 FOR JSON 子句指定 ROOT 选项,将一个顶层元素添加到 JSON 输出中 。Add a single, top-level element to the JSON output by specifying the ROOT option with the FOR JSON clause. 如果没有指定 ROOT 选项,则 JSON 输出不会包括根元素。If you don't specify the ROOT option, the JSON output doesn't have a root element. 有关详细信息和示例,请参阅使用 ROOT 选项将根节点添加到 JSON 输出中 (SQL Server)For more info and examples, see Add a Root Node to JSON Output with the ROOT Option (SQL Server).

WITHOUT_ARRAY_WRAPPERWITHOUT_ARRAY_WRAPPER
通过使用 FOR JSON 子句指定 WITHOUT_ARRAY_WRAPPER 选项,删除默认括住 JSON 输出的方括号 。Remove the square brackets that surround the JSON output by default by specifying the WITHOUT_ARRAY_WRAPPER option with the FOR JSON clause. 如果不指定此选项,JSON 输出将括在方括号中。If you don't specify this option, the JSON output is enclosed within square brackets. 使用 WITHOUT_ARRAY_WRAPPER 选项可以生成单个 JSON 对象作为输出。Use the WITHOUT_ARRAY_WRAPPER option to generate a single JSON object as output. 有关详细信息,请参阅 使用 WITHOUT_ARRAY_WRAPPER 选项从 JSON 输出中删除方括号 (SQL Server)For more info, see Remove Square Brackets from JSON Output with the WITHOUT_ARRAY_WRAPPER Option (SQL Server).

有关详细信息,请参阅借助 FOR JSON 将查询结果格式化为 JSON (SQL Server)For more info, see Format Query Results as JSON with FOR JSON (SQL Server).

另请参阅See Also

SELECT (Transact-SQL)SELECT (Transact-SQL)