SQL查询语句（四）—— 子查询

一、子查询

一个MySQL子查询是嵌套在另一个查询内

• 包含子查询的查询称为外部查询
• 子查询称为内部查询

查询返回在美国办公室工作的员工

SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');

• 子查询返回位于美国的办事处的所有办事处代码。
• 外部查询选择在办公室工作的员工的姓氏和名字，这些员工的办公室代码在子查询返回的结果集中。

比较运算符 子查询

使用比较运算符（例如=，>，<）将子查询返回的单个值与WHERE子句中的表达式进行比较

查询返回付款最高的客户

SELECT 
    customerNumber, 
    checkNumber, 
    amount
FROM
    payments
WHERE
    amount = (SELECT MAX(amount) FROM payments);

子查询返回的结果集将用作临时表

查询查找销售订单中项目的最大数量，最小数量和平均数量

SELECT 
    MAX(items), 
    MIN(items), 
    FLOOR(AVG(items))
FROM
    (SELECT 
        orderNumber, COUNT(orderNumber) AS items
    FROM
        orderdetails
    GROUP BY orderNumber) AS lineitems;

EXISTS 子查询

MySQL Subquery

二、派生表

派生表是从SELECT语句返回的虚拟表。

派生表必须具有别名，如果没有别名会出现错误

Every derived table must have its own alias.

示例：

SELECT 
    productName, sales
FROM
    (SELECT 
        productCode, 
        ROUND(SUM(quantityOrdered * priceEach)) sales
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY productCode
    ORDER BY sales DESC
    LIMIT 5) top5products2003
INNER JOIN
    products USING (productCode);

1. 首先，执行子查询以创建结果集或派生表。
2. 然后，执行外部查询，该查询使用列将top5product2003派生表与products表联接在一起productCode。

示例2：

1. 订单量超过10万的白金客户
2. 订单量在10K到100K之间的金牌客户
3. 订单量小于1万的白银客户

SELECT 
    customerNumber,
    ROUND(SUM(quantityOrdered * priceEach)) sales,
    (CASE
        WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
        WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
        WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
    END) customerGroup
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2003
GROUP BY customerNumber;

查询返回客户组以及每个客户组中的客户数量

SELECT 
    customerGroup, 
    COUNT(cg.customerGroup) AS groupCount
FROM
    (SELECT 
        customerNumber,
            ROUND(SUM(quantityOrdered * priceEach)) sales,
            (CASE
                WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
                WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
                WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
            END) customerGroup
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;    

三、EXISTS

https://www.mysqltutorial.org/mysql-exists/

参考资料

[basic-mysql-tutorial](https://www.mysqltutorial.org/basic-mysql-tutorial.aspx