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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

從資料庫中擷取資料列,可讓您從 SQL ServerSQL Server中的一個或多個資料表選取一個或多個資料列或資料行。Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL ServerSQL Server. SELECT 陳述式的完整語法很複雜,但主要子句可摘要如下:The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:

[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ][ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ]

SELECT select_list [ INTO new_table ]SELECT select_list [ INTO new_table ]

[ FROM table_source ] [ WHERE search_condition ][ FROM table_source ] [ WHERE search_condition ]

[ GROUP BY group_by_expression ][ GROUP BY group_by_expression ]

[ HAVING search_condition ][ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ][ ORDER BY order_expression [ ASC | DESC ] ]

您可以在查詢之間使用 UNION、EXCEPT 和 INTERSECT 運算子來比較它們的結果,或將它們的結果結合成單一結果集。The UNION, EXCEPT, and INTERSECT operators can be used between queries to combine or compare their results into one result set.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
<SELECT statement> ::=    
    [ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]  
    <query_expression>   
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }   
  [ ,...n ] ]   
    [ <FOR Clause>]   
    [ OPTION ( <query_hint> [ ,...n ] ) ]   
<query_expression> ::=   
    { <query_specification> | ( <query_expression> ) }   
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }  
        <query_specification> | ( <query_expression> ) [...n ] ]   
<query_specification> ::=   
SELECT [ ALL | DISTINCT ]   
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ]   
    < select_list >   
    [ INTO new_table ]   
    [ FROM { <table_source> } [ ,...n ] ]   
    [ WHERE <search_condition> ]   
    [ <GROUP BY> ]   
    [ HAVING < search_condition > ]   
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
[ WITH <common_table_expression> [ ,...n ] ]  
SELECT <select_criteria>  
[;]  
  
<select_criteria> ::=  
    [ TOP ( top_expression ) ]   
    [ ALL | DISTINCT ]   
    { * | column_name | expression } [ ,...n ]   
    [ FROM { table_source } [ ,...n ] ]  
    [ WHERE <search_condition> ]   
    [ GROUP BY <group_by_clause> ]   
    [ HAVING <search_condition> ]   
    [ ORDER BY <order_by_expression> ]  
    [ OPTION ( <query_option> [ ,...n ] ) ]  
  

RemarksRemarks

由於 SELECT 陳述式十分複雜,因此將會依子句顯示詳細的語法元素及引數:Because of the complexity of the SELECT statement, detailed syntax elements and arguments are shown by clause:

WITH XMLNAMESPACESWITH XMLNAMESPACES

WITH common_table_expressionWITH common_table_expression
HAVINGHAVING
SELECT 子句SELECT Clause UNIONUNION
INTO 子句INTO Clause EXCEPT 和 INTERSECTEXCEPT and INTERSECT
FROMFROM ORDER BYORDER BY
WHEREWHERE FOR 子句FOR Clause
GROUP BYGROUP BY OPTION 子句OPTION Clause

子句順序對 SELECT 陳述式而言十分重要。The order of the clauses in the SELECT statement is significant. 您可以省略任何選擇性的子句,但當使用選擇性的子句時,它們必須以適當的順序顯示。Any one of the optional clauses can be omitted, but when the optional clauses are used, they must appear in the appropriate order.

只有在這些 SELECT 陳述式的選取清單包含指派使用者自訂函數之本機變數值的運算式時,才能在使用者自訂函數中,允許 SELECT 陳述式。SELECT statements are permitted in user-defined functions only if the select lists of these statements contain expressions that assign values to variables that are local to the functions.

利用 OPENDATASOURCE 函數來建構成伺服器名稱部分的四部分名稱,每當 SELECT 陳述式中出現資料表名稱時,都可用來做為資料表來源。A four-part name constructed with the OPENDATASOURCE function as the server-name part can be used as a table source wherever a table name can appear in a SELECT statement. 針對 Azure SQL DatabaseAzure SQL Database,不可指定四部分名稱。A four-part name cannot be specified for Azure SQL DatabaseAzure SQL Database.

部分語法限制只適用於牽涉到遠端資料表的 SELECT 陳述式。Some syntax restrictions apply to SELECT statements that involve remote tables.

SELECT 陳述式的邏輯處理順序Logical Processing Order of the SELECT statement

下列步驟顯示 SELECT 陳述式的邏輯處理順序或繫結順序。The following steps show the logical processing order, or binding order, for a SELECT statement. 此順序會決定在某個步驟中定義的物件提供給後續步驟之子句使用的時間。This order determines when the objects defined in one step are made available to the clauses in subsequent steps. 例如,如果查詢處理器可繫結至 (存取) FROM 子句中定義的資料表或檢視表,這些物件及其資料行就會提供給所有後續步驟使用。For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. 反之,因為 SELECT 子句是步驟 8,所以前面的子句無法參考該子句中定義的任何資料行別名或衍生資料行。Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. 不過,ORDER BY 子句等後續子句都可以參考它們。However, they can be referenced by subsequent clauses such as the ORDER BY clause. 陳述式的實際執行方式由查詢處理器決定,其順序可能與此清單不同。The actual physical execution of the statement is determined by the query processor and the order may vary from this list.

  1. FROMFROM
  2. ONON
  3. JOINJOIN
  4. WHEREWHERE
  5. GROUP BYGROUP BY
  6. WITH CUBE 或 WITH ROLLUPWITH CUBE or WITH ROLLUP
  7. HAVINGHAVING
  8. SELECTSELECT
  9. DISTINCTDISTINCT
  10. ORDER BYORDER BY
  11. 頂端TOP

警告

通常會按照上述順序。The preceding sequence is usually true. 不過,在一些不常見的情況下,順序可能會有不同。However, there are uncommon cases where the sequence may differ.

例如,假設您在檢視表上有叢集索引,而該檢視表排除某些資料表資料列,且檢視表的 SELECT 資料行清單使用 CONVERT 將資料類型從 varchar 變更為 integerFor example, suppose you have a clustered index on a view, and the view excludes some table rows, and the view's SELECT column list uses a CONVERT that changes a data type from varchar to integer. 在此情況下,CONVERT 的執行順序可能會在 WHERE 子句之前。In this situation, the CONVERT may execute before the WHERE clause executes. 這確實是不常見的情況。Uncommon indeed. 如果在您的案例中順序相當重要,通常可以修改您的檢視表來避免順序不同。Often there is a way to modify your view to avoid the different sequence, if it matters in your case.

[權限]Permissions

選取資料需要資料表或檢視的 SELECT 權限;此權限可從較高的範圍繼承而來,例如結構描述的 SELECT 權限或資料表的 CONTROL 權限。Selecting data requires SELECT permission on the table or view, which could be inherited from a higher scope such as SELECT permission on the schema or CONTROL permission on the table. 或是需要 db_datareaderdb_owner 固定資料庫角色中的成員資格,或 sysadmin 固定伺服器角色中的成員資格。Or requires membership in the db_datareader or db_owner fixed database roles, or the sysadmin fixed server role. 使用 SELECTINTO 來建立新資料表也需要 CREATETABLE 權限,以及擁有新資料表之結構描述上的 ALTERSCHEMA 權限。Creating a new table using SELECTINTO also requires both the CREATETABLE permission, and the ALTERSCHEMA permission on the schema that owns the new table.

範例:Examples:

下列範例使用 AdventureWorksPDW2012AdventureWorksPDW2012 資料庫。The following examples use the AdventureWorksPDW2012AdventureWorksPDW2012 database.

A.A. 使用 SELECT 擷取資料列和資料行Using SELECT to retrieve rows and columns

本節將示範三個程式碼範例。This section shows three code examples. 第一個程式碼範例會從 DimEmployee 資料表中,傳回所有資料列 (未指定 WHERE 子句) 和所有資料行 (使用 *)。This first code example returns all rows (no WHERE clause is specified) and all columns (using the *) from the DimEmployee table.

SELECT *  
FROM DimEmployee  
ORDER BY LastName;  

這個接下來的範例會使用資料表別名來達到相同的結果。This next example using table aliasing to achieve the same result.

SELECT e.*  
FROM DimEmployee AS e  
ORDER BY LastName;  

此範例會從 AdventureWorksPDW2012 資料庫的 DimEmployee 資料表中,傳回所有資料列 (未指定 WHERE 子句),以及資料行子集 (FirstNameLastNameStartDate)。This example returns all rows (no WHERE clause is specified) and a subset of the columns (FirstName, LastName, StartDate) from the DimEmployee table in the AdventureWorksPDW2012 database. 第三個資料行標題會重新命名為 FirstDayThe third column heading is renamed to FirstDay.

SELECT FirstName, LastName, StartDate AS FirstDay  
FROM DimEmployee   
ORDER BY LastName;  

此範例只會傳回 EndDate 不是 NULL 且 MaritalStatus 是 ‘M’ (已婚) 的 DimEmployee 資料列。This example returns only the rows for DimEmployee that have an EndDate that is not NULL and a MaritalStatus of 'M' (married).

SELECT FirstName, LastName, StartDate AS FirstDay  
FROM DimEmployee   
WHERE EndDate IS NOT NULL   
AND MaritalStatus = 'M'  
ORDER BY LastName;  

B.B. 使用 SELECT 與資料行標題及計算Using SELECT with column headings and calculations

下列範例會從 DimEmployee 資料表中傳回所有資料列,並根據每位員工的 BaseRate 和 40 小時工作週來計算其總薪資。The following example returns all rows from the DimEmployee table, and calculates the gross pay for each employee based on their BaseRate and a 40-hour work week.

SELECT FirstName, LastName, BaseRate, BaseRate * 40 AS GrossPay  
FROM DimEmployee  
ORDER BY LastName;  

C.C. 使用 DISTINCT 與 SELECTUsing DISTINCT with SELECT

下列範例會使用 DISTINCT來產生 DimEmployee 資料表中所有唯一職稱的清單。The following example uses DISTINCT to generate a list of all unique titles in the DimEmployee table.

SELECT DISTINCT Title  
FROM DimEmployee  
ORDER BY Title;  

D.D. 使用 GROUP BYUsing GROUP BY

下列範例會尋找每天的所有銷售總額。The following example finds the total amount for all sales on each day.

SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey  
ORDER BY OrderDateKey;  

由於 GROUP BY 子句的緣故,因此只會針對每天,傳回一個包含所有銷售總和的資料列。Because of the GROUP BY clause, only one row containing the sum of all sales is returned for each day.

E.E. 使用 GROUP BY 與多個群組Using GROUP BY with multiple groups

下列範例會尋找每天網際網路銷售的平均價格和總和,並依照訂單日期和促銷索引鍵分組。The following example finds the average price and the sum of Internet sales for each day, grouped by order date and the promotion key.


SELECT OrderDateKey, PromotionKey, AVG(SalesAmount) AS AvgSales, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey, PromotionKey  
ORDER BY OrderDateKey;   

F.F. 使用 GROUP BY 與 WHEREUsing GROUP BY and WHERE

下列範例會在只擷取訂單日期晚於 2002 年 8 月 1 日的資料列之後,將結果分組。The following example puts the results into groups after retrieving only the rows with order dates later than August 1, 2002.

SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
WHERE OrderDateKey > '20020801'  
GROUP BY OrderDateKey  
ORDER BY OrderDateKey;  

G.G. 使用 GROUP BY 與運算式Using GROUP BY with an expression

下列範例會依運算式來分組。The following example groups by an expression. 如果運算式不包括彙總函式,您可以依運算式來進行分組。You can group by an expression if the expression does not include aggregate functions.

SELECT SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY (OrderDateKey * 10);  

H.H. 使用 GROUP BY 與 ORDER BYUsing GROUP BY with ORDER BY

下列範例會尋找每天的銷售總和,然後依照日期排序。The following example finds the sum of sales per day, and orders by the day.

SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey  
ORDER BY OrderDateKey;  

I.I. 使用 HAVING 子句Using the HAVING clause

此查詢會使用 HAVING 子句來限制結果。This query uses the HAVING clause to restrict results.

SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey  
HAVING OrderDateKey > 20010000  
ORDER BY OrderDateKey;  

另請參閱See Also

SELECT 範例 (Transact-SQL)SELECT Examples (Transact-SQL)
提示 (Transact-SQL)Hints (Transact-SQL)