T Sql 简明教程

T-SQL - Sub-Queries

sub-query 、 Inner query 或 Nested query 是另一个 SQL Server 查询内的一个查询，并且嵌入在 WHERE 子句中。子查询用于返回将用作主查询中一个条件的数据，以便进一步限制要检索的数据。

A sub-query or Inner query or Nested query is a query within another SQL Server query and embedded within the WHERE clause. A sub query is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

子查询可与 SELECT、INSERT、UPDATE 和 DELETE 语句以及 =、<、>、>=、⇐、IN、BETWEEN 等运算符搭配使用。

Sub queries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, ⇐, IN, BETWEEN, etc.

子查询必须遵循一些规则：

There are a few rules that sub queries must follow −

You must enclose a subquery in parenthesis.
A subquery must include a SELECT clause and a FROM clause.
A subquery can include optional WHERE, GROUP BY, and HAVING clauses.
A subquery cannot include COMPUTE or FOR BROWSE clauses.
You can include an ORDER BY clause only when a TOP clause is included.
You can nest sub queries up to 32 levels.

Subqueries with SELECT Statement

Syntax

子查询最常与 SELECT 语句一起使用。以下是基本语法。

Subqueries are most frequently used with the SELECT statement. Following is the basic syntax.

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
   (SELECT column_name [, column_name ]
   FROM table1 [, table2 ]
   [WHERE])

Example

可以将 CUSTOMERS 表视为包含以下记录。

Consider the CUSTOMERS table having the following records.

ID  NAME       AGE       ADDRESS            SALARY
1   Ramesh     32        Ahmedabad          2000.00
2   Khilan     25        Delhi              1500.00
3   kaushik    23        Kota               2000.00
4   Chaitali   25        Mumbai             6500.00
5   Hardik     27        Bhopal             8500.00
6   Komal      22        MP                 4500.00
7   Muffy      24        Indore             10000.00

让我们对 SELECT 语句应用以下子查询。

Let us apply the following subquery with SELECT statement.

SELECT *
   FROM CUSTOMERS
   WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500)

以上命令将生成以下输出。

The above command will produce the following output.

ID  NAME       AGE       ADDRESS          SALARY
4   Chaitali   25        Mumbai           6500.00
5   Hardik     27        Bhopal           8500.00
7   Muffy      24        Indore           10000.00

Subqueries with INSERT Statement

子查询还可以与 INSERT 语句一起使用。INSERT 语句使用从子查询返回的数据插入另一个表。子查询中所选的数据可以使用任何字符、日期或数字函数进行修改。

Sub queries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions.

Syntax

以下是基本语法。

Following is the basic syntax.

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

Example

考虑一个结构与 CUSTOMERS 表相似的表 CUSTOMERS_BKP。以下是可以将完整的 CUSTOMERS 表复制到 CUSTOMERS_BKP 的语法。

Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table. Following is the syntax to copy complete CUSTOMERS table into CUSTOMERS_BKP.

INSERT INTO CUSTOMERS_BKP
   SELECT * FROM CUSTOMERS
   WHERE ID IN (SELECT ID FROM CUSTOMERS)

Subqueries with UPDATE Statement

子查询可以与 UPDATE 语句联合使用。在使用 UPDATE 语句与子查询时，可以更新表中的单个或多个列。

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.

Syntax

以下是基本语法。

Following is the basic syntax.

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example

我们假设我们有可用的是 CUSTOMERS_BKP 表，它是 CUSTOMERS 表的备份。

Let us assume we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table.

以下命令示例将对 CUSTOMERS 表中所有年龄大于或等于 27 的客户更新 SALARY，更新幅度为 0.25 倍。

Following command example updates SALARY by 0.25 times in CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.

UPDATE CUSTOMERS
   SET SALARY = SALARY * 0.25
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 )

这将影响两行，并且最终 CUSTOMERS 表将拥有以下记录。

This will impact two rows and finally CUSTOMERS table will have the following records.

ID  NAME       AGE       ADDRESS             SALARY
1   Ramesh     32        Ahmedabad           500.00
2   Khilan     25        Delhi               1500.00
3   kaushik    23        Kota                2000.00
4   Chaitali   25        Mumbai              6500.00
5   Hardik     27        Bhopal              2125.00
6   Komal      22        MP                  4500.00
7   Muffy      24        Indore              10000.00

Subqueries with DELETE Statement

与上面提到的其他任何语句一样，子查询可与 DELETE 语句一起使用。

The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.

Syntax

以下是基本语法。

Following is the basic syntax.

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example

我们假设我们有可用的是 CUSTOMERS_BKP 表，它是 CUSTOMERS 表的备份。

Let us assume we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table.

以下命令示例将删除 CUSTOMERS 表中所有年龄大于或等于 27 的客户的记录。

Following command example deletes records from CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.

DELETE FROM CUSTOMERS
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >=27 )

这将影响两行，并且最终 CUSTOMERS 表将拥有以下记录。

This would impact two rows and finally CUSTOMERS table will have the following records.

ID  NAME       AGE       ADDRESS          SALARY
2   Khilan     25        Delhi            1500.00
3   kaushik    23        Kota             2000.00
4   Chaitali   25        Mumbai           6500.00
6   Komal      22        MP               4500.00
7   Muffy      24        Indore           10000.00