项目三非相关子查询应用 - 3.2 非相关子查询二 - 《MySQL高级应用》

学习目标：

1.理解什么是多行子查询？

2.多行子查询的几种情形及使用场景。

3.多行子查询用在where子句中的比较符

4.select子句中使用子查询

5.from子句中使用子查询

一、新知学习

1.什么是多行子查询？

返回多行单列或多行多列的查询结果。

2.使用场景：多行单列多用于where子句，

比较符: in 决不能用“=”，可以替换”=”

all、any|some，不能使用单值比较符“> < ……”

-- 1.查询“江汉路”、“司门口”和“西北湖”地区的商品信息，
-- 要求输出商品标题、商品团购价和区域编号。
-- 分析：
-- 1.2 主查询：在product表中查询areaID是子查询的结果的
-- 商品信息
SELECT p.title 商品标题, p.currentPrice 商品团购价, p.areaID 区域编号
FROM product p
WHERE p.areaID in  -- 不能用 =[Err] 1242 - Subquery returns more than 1 row
    -- 1.1 子查询：在area表中查询“江汉路”、“司门口”和
    -- “西北湖”地区的areaID
    (SELECT a.areaID  -- 返回多行1列
    FROM area a
    WHERE a.areaName in('江汉路','司门口','武广'));
-- 2.查询所有男性顾客所购商品信息，要求输出商品标题和商品团购价，
-- 并按照团购价升序排列。
SELECT p.title 商品标题,p.currentPrice 商品团购价
from product p
WHERE p.productID in (
    -- 2.3查询男性客户所订购的商品ID
    SELECT od.productID
    FROM ordersdetail od
    WHERE od.ordersID in
            -- 2.2依据2.1的结果在orders表查询男性订单编号
        (SELECT o.ordersID
        FROM orders o
        WHERE o.customerID in
            -- 2.1找男性客户的customerID
            (SELECT c.customerID
            FROM customer c
            where c.gender='男')))
order by p.currentPrice;

-- ALL 用法  >ALL(子查询)  大于所有值
-- <ALL(子查询)  小于所有值
-- 查询团购价比所有服装类商品团购价都高的商品信息，
-- 要求输出商品标题和商品团购价。
SELECT p.title 商品标题,p.currentPrice 商品团购价
FROM product p
WHERE p.currentPrice >ALL (
    (SELECT p.currentPrice  -- 多行子查询不能用 >
    from product p
    WHERE p.categoryID in
        (select cg.categoryID
        from category cg
        WHERE cg.categoryName='火锅')));
-- !=ALL 就是NOT in 不在其中的
-- =ALL 永远为空值
SELECT p.title 商品标题,p.currentPrice 商品团购价
FROM product p
WHERE p.currentPrice = ALL (
    (SELECT p.currentPrice  -- 多行子查询不能用 >
    from product p
    WHERE p.categoryID in
        (select cg.categoryID
        from category cg
        WHERE cg.categoryName='火锅')));
-- ANY|SOME >ANY 大于最小的值
SELECT p.title 商品标题,p.currentPrice 商品团购价
FROM product p
WHERE p.currentPrice >ANY (
    (SELECT p.currentPrice  -- 多行子查询不能用 >
    from product p
    WHERE p.categoryID in
        (select cg.categoryID
        from category cg
        WHERE cg.categoryName='火锅')));
-- <ANY 小于最大的
SELECT p.title 商品标题,p.currentPrice 商品团购价
FROM product p
WHERE p.currentPrice <ANY (
    (SELECT p.currentPrice  -- 多行子查询不能用 >
    from product p
    WHERE p.categoryID in
        (select cg.categoryID
        from category cg
        WHERE cg.categoryName='火锅')));
-- =ANY 等价于 IN
SELECT p.title 商品标题,p.currentPrice 商品团购价
FROM product p
WHERE p.currentPrice =ANY (
    (SELECT p.currentPrice  -- 多行子查询不能用 >
    from product p
    WHERE p.categoryID in
        (select cg.categoryID
        from category cg
        WHERE cg.categoryName='火锅')));
-- !=ANY 等价于NOT in
SELECT p.title 商品标题,p.currentPrice 商品团购价
FROM product p
WHERE p.currentPrice !=ANY (
    (SELECT p.currentPrice  -- 多行子查询不能用 >
    from product p
    WHERE p.categoryID in
        (select cg.categoryID
        from category cg
        WHERE cg.categoryName='火锅')));

3.From子句使用多行多列子查询（表）：

-- FROM 子句使用子查询（多行多列--虚拟表）
-- 为帮助商家提升定价能力，优化产品销售策略，
-- 美淘网平台在向每位商家提供产品信息的同时，
-- 还提供了该类商品的平均团购价。
-- 1.1 在product表，按categoryID分类统计商品的平均currentPrice
-- 多行子查询数据不全，看成一张表
SELECT c.categoryName ,t.`平均团购价`
FROM category c
NATURAL JOIN   -- 将子查询看作表进行联接补充数据
    (SELECT p.categoryID , AVG( p.currentPrice) 平均团购价 -- 此处一定要把表达式取别名
    FROM product  p
    GROUP BY p.categoryID) t;

4.Select子句中使用子查询（单个值）

SELECT
    (SELECT COUNT(*) 商品数
    FROM product p) 商品数,
    (SELECT COUNT(DISTINCT productID) 已订购商品个数
    FROM ordersdetail ) 已订购商品个数,
    (SELECT SUM(quantity) 已订购商品件数
    FROM ordersdetail ) 已订购商品件数

小结：