SELECT product_type, cnt_product
FROM (SELECT*FROM (SELECT product_type,
COUNT(*) AS cnt_product
FROM product
GROUPBY product_type) AS productsum
WHERE cnt_product =4) AS productsum2;
SELECT product_id, product_name, sale_price
FROM product
WHERE sale_price > (SELECTAVG(sale_price) FROM product);
关联子查询
关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的
SELECT product_type, product_name, sale_price
FROM product AS p1
WHERE sale_price > (SELECTAVG(sale_price)
FROM product AS p2
WHERE p1.product_type = p2.product_type
GROUPBY product_type);
SELECT product_name, sale_price
FROM product AS p
WHEREEXISTS (SELECT*FROM shopproduct AS sp
WHERE sp.shop_id ='000C'AND sp.product_id = p.product_id);
EXIST的参数
EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询
(SELECT*FROM shopproduct AS sp
WHERE sp.shop_id ='000C'AND sp.product_id = p.product_id)
子查询中的SELECT *
由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 '000C'，商品（product）表和商店
SELECT product_name, sale_price
FROM product AS p
WHEREEXISTS (SELECT1-- 这里可以书写适当的常数FROM shopproduct AS sp
WHERE sp.shop_id ='000C'AND sp.product_id = p.product_id);
大家可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯。
使用NOT EXIST替换NOT IN
SELECT product_name, sale_price
FROM product AS p
WHERENOTEXISTS (SELECT*FROM shopproduct AS sp
WHERE sp.shop_id ='000A'AND sp.product_id = p.product_id);
上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。
无论多么庞大的 CASE 表达式，最后也只会返回一个值
CASE表达式的使用方法
应用场景1：根据不同分支得到不同列值
SELECT product_name,
CASEWHEN product_type ='衣服'THEN CONCAT('A ： ',product_type)
WHEN product_type ='办公用品'THEN CONCAT('B ： ',product_type)
WHEN product_type ='厨房用具'THEN CONCAT('C ： ',product_type)
ELSENULLENDAS abc_product_type
FROM product;
ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。防止漏读，建议显示地写出 ELSE 子句。
CASE 表达式最后的“END”是不能省略的，忘记书写 END 会发生语法错误
应用场景2：实现列方向上的聚合
通常我们使用如下代码实现行的方向上不同种类的聚合（这里是 sum）
SELECT product_type,
SUM(sale_price) AS sum_price
FROM product
GROUPBY product_type;
假如要在列的方向上展示不同种类额聚合值，该如何写呢？
-- 对按照商品种类计算出的销售单价合计值进行行列转换SELECTSUM(CASEWHEN product_type ='衣服'THEN sale_price ELSE0END) AS sum_price_clothes,
SUM(CASEWHEN product_type ='厨房用具'THEN sale_price ELSE0END) AS sum_price_kitchen,
SUM(CASEWHEN product_type ='办公用品'THEN sale_price ELSE0END) AS sum_price_office
FROM product;
应用场景3：实现行转列
聚合函数 + CASE WHEN 表达式即可实现该转换
-- CASE WHEN 实现数字列 score 行转列SELECT name,
SUM(CASEWHEN subject ='语文'THEN score ELSEnullEND) as chinese,
SUM(CASEWHEN subject ='数学'THEN score ELSEnullEND) as math,
SUM(CASEWHEN subject ='外语'THEN score ELSEnullEND) as english
FROM score
GROUPBY name;
上述代码实现了数字列 score 的行转列，也可以实现文本列 subject 的行转列
-- CASE WHEN 实现文本列 subject 行转列SELECT name,
MAX(CASEWHEN subject ='语文'THEN subject ELSEnullEND) as chinese,
MAX(CASEWHEN subject ='数学'THEN subject ELSEnullEND) as math,
MIN(CASEWHEN subject ='外语'THEN subject ELSEnullEND) as english
FROM score
GROUPBY name;
复制代码
