子查询

项目
05/23/2023

适用于：Azure Synapse Analytics Analytics Platform System (PDW)Microsoft Fabric 中的 SQL 分析端点 Microsoft Fabric 中的仓库

本主题提供在 Azure Synapse Analytics、Analytics Platform System (PDW) 或 Microsoft Fabric 中使用子查询的示例。

有关 SELECT 语句，请参阅 SELECT (Transact-SQL)

基础

子查询
子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。子查询亦称内部查询或内部选择。

外部查询
包含子查询的语句，亦称外部选择。

相关子查询
引用外部查询中的表的子查询。

示例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

本节提供 Azure Synapse Analytics或Analytics Platform System (PDW)支持的子查询的示例。

A. 子查询中的 TOP 和 ORDER BY

SELECT * FROM tblA  
WHERE col1 IN  
    (SELECT TOP 100 col1 FROM tblB ORDER BY col1);

B. 包含相关子查询的 HAVING 子句

SELECT dm.EmployeeKey, dm.FirstName, dm.LastName   
FROM DimEmployee AS dm   
GROUP BY dm.EmployeeKey, dm.FirstName, dm.LastName  
HAVING 5000 <=   
(SELECT sum(OrderQuantity)  
FROM FactResellerSales AS frs  
WHERE dm.EmployeeKey = frs.EmployeeKey)  
ORDER BY EmployeeKey;

C. 使用分析的相关子查询

SELECT * FROM ReplA AS A   
WHERE A.ID IN   
    (SELECT sum(B.ID2) OVER() FROM ReplB AS B WHERE A.ID2 = B.ID);

D. 子查询中的相关联合语句

SELECT * FROM RA   
WHERE EXISTS   
    (SELECT 1 FROM RB WHERE RB.b1 = RA.a1   
     UNION ALL SELECT 1 FROM RC);

E. 子查询中的联接谓词

SELECT * FROM RA INNER JOIN RB   
    ON RA.a1 = (SELECT COUNT(*) FROM RC);

F. 子查询中的相关联接谓词

SELECT * FROM RA   
    WHERE RA.a2 IN   
    (SELECT 1 FROM RB INNER JOIN RC ON RA.a1=RB.b1+RC.c1);

G. 作为数据源的相关嵌套 select 语句

SELECT * FROM RA   
    WHERE 3 = (SELECT COUNT(*)   
        FROM (SELECT b1 FROM RB WHERE RB.b1 = RA.a1) X);

H. 用于聚合的数据值中的相关子查询

SELECT Rb.b1, (SELECT RA.a1 FROM RA WHERE RB.b1 = RA.a1) FROM RB GROUP BY RB.b1;

I. 在相关子查询中使用 IN

以下示例在相关或重复子查询中使用 IN。该查询依赖于外部查询来查询其值。该内部查询为外部查询可能选择的每一行各重复运行一次。该查询检索 FactResellerSales 表中 OrderQuantity 为 5 且雇员标识号在 DimEmployee 和 FactResellerSales 表中相匹配的每个雇员的 EmployeeKey 加姓名的一个实例。

SELECT DISTINCT dm.EmployeeKey, dm.FirstName, dm.LastName   
FROM DimEmployee AS dm   
WHERE 5 IN   
    (SELECT OrderQuantity  
    FROM FactResellerSales AS frs  
    WHERE dm.EmployeeKey = frs.EmployeeKey)  
ORDER BY EmployeeKey;

J. 在子查询中使用 EXISTS 和 IN

以下示例展示了语义等效的查询，以说明使用 EXISTS 关键字和 IN 关键字的区别。两者都是子查询示例，用于检索产品子类别为 Road Bikes 的每个产品名称的一个实例。 ProductSubcategoryKey 在 DimProduct 和 DimProductSubcategory 表之间进行匹配。

SELECT DISTINCT EnglishProductName  
FROM DimProduct AS dp   
WHERE EXISTS  
    (SELECT *  
     FROM DimProductSubcategory AS dps   
     WHERE dp.ProductSubcategoryKey = dps.ProductSubcategoryKey  
           AND dps.EnglishProductSubcategoryName = 'Road Bikes')  
ORDER BY EnglishProductName;

或

SELECT DISTINCT EnglishProductName  
FROM DimProduct AS dp   
WHERE dp.ProductSubcategoryKey IN  
    (SELECT ProductSubcategoryKey  
     FROM DimProductSubcategory   
     WHERE EnglishProductSubcategoryName = 'Road Bikes')  
ORDER BY EnglishProductName;

K. 使用多个相关子查询

此示例使用两个相关子查询查找售出过某种特定产品的雇员的姓名。

SELECT DISTINCT LastName, FirstName, e.EmployeeKey  
FROM DimEmployee e JOIN FactResellerSales s ON e.EmployeeKey = s.EmployeeKey  
WHERE ProductKey IN  
(SELECT ProductKey FROM DimProduct WHERE ProductSubcategoryKey IN  
(SELECT ProductSubcategoryKey FROM DimProductSubcategory   
 WHERE EnglishProductSubcategoryName LIKE '%Bikes'))  
ORDER BY LastName;

子查询

目录

基础