Teradata 简明教程

Teradata - SubQueries

子查询基于其他表中的值从一个表中返回记录。它是另一个查询中的 SELECT 查询。称为内部查询的 SELECT 查询首先执行，而结果则由外部查询使用。其一些显著特征：

A subquery returns records from one table based on the values from another table. It is a SELECT query within another query. The SELECT query called as inner query is executed first and the result is used by the outer query. Some of its salient features are −

A query can have multiple subqueries and subqueries may contain another subquery.
Subqueries doesn’t return duplicate records.
If subquery returns only one value, you can use = operator to use it with the outer query. If it returns multiple values you can use IN or NOT IN.

Syntax

以下是子查询的通用语法。

Following is the generic syntax of subqueries.

SELECT col1, col2, col3,…
FROM
Outer Table
WHERE col1 OPERATOR ( Inner SELECT Query);

Example

考虑以下 Salary 表格。

Consider the following Salary table.

EmployeeNo	Gross	Deduction	NetPay
101	40,000	4,000	36,000
102	80,000	6,000	74,000
103	90,000	7,000	83,000
104	75,000	5,000	70,000

以下查询标识出具有最高工资的员工编号。内部 SELECT 执行聚合函数以返回最大的 NetPay 值，而外部 SELECT 查询使用此值返回具有此值的员工记录。

The following query identifies the employee number with highest salary. The inner SELECT performs the aggregation function to return the maximum NetPay value and the outer SELECT query uses this value to return the employee record with this value.

SELECT EmployeeNo, NetPay
FROM Salary
WHERE NetPay =
(SELECT MAX(NetPay)
FROM Salary);

执行此查询时，将得到以下输出。

When this query is executed, it produces the following output.

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.
 EmployeeNo     NetPay
-----------  -----------
    103         83000