常用的SQL语句小结（三）---复杂查询，CASE表达式，各种连接查询

1.复杂查询

（1）普通子查询

SELECT product_type, cnt_product
FROM (
SELECT Product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type
) AS ProductSum;

（）里的是内层查询会首先执行，然后才会执行外层查询

子查询可以多层嵌套，但是性能会下降，尽量少用多层子查询

 

（2）标量子查询

标量子查询只返回一列一行

 错误案例：选取出销售单价（ sale_price）高于全部商品的平均单价的商品

 

SELECT product_id, product_name, sale_price FROM Product
WHERE sale_price > AVG(sale_price);

WHERE子句不能直接使用聚合函数

正确做法:

SELECT product_id, product_name, sale_price FROM Product
WHERE sale_price > (SELECT AVG(sale_price) FROM Product);

 

（3）关联子查询

案例：选取出各商品种类中高于该商品种类的平均销售单价的商品

第一步：按照商品种类计算平均价格
SELECT AVG(sale_price) FROM Product GROUP BY product_type; 
第二步：在细分的组内进行比较时，需要使用关联子查询

SELECT product_type, product_name, sale_price FROM Product AS P1
WHERE sale_price > (SELECT AVG(sale_price) FROM Product AS P2
WHERE P1.product_type = P2.product_type GROUP BY product_type);

GROUP BY product_type去掉也能得到正确结果，因为P1.product_type = P2.product_type这个条件已经相当于AVG函数按照商品种类来计算了

 

（4）行列转换 CASE表达式

-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服'
THEN sale_price ELSE 0 END) AS sum_price_clothes,
SUM(CASE WHEN product_type = '厨房用具'
THEN sale_price ELSE 0 END) AS sum_price_kitchen,
SUM(CASE WHEN product_type = '办公用品'
THEN sale_price ELSE 0 END) AS sum_price_office
FROM Product;

在满足商品种类（product_type）为“衣服”或者“办公用品”
等特定值时，上述 CASE 表达式输出该商品的销售单价（sale_price），
不满足时输出 0。对该结果进行汇总处理，就能够得到特定商品种类的销
售单价合计值了。

（5）UNION和UNION ALL区别

SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;

UNION会合并重复行为一行，而UNION ALL不会合并

UNION相当于表的加法，INTERSECT （交集），EXCEPT (减法，Product有而Product2没有)

 

（6）内连接示例

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name,
P.sale_price
FROM ShopProduct AS SP INNER JOIN Product AS P
ON SP.product_id = P.product_id
WHERE SP.shop_id = '000A';

（7）右外连接示例（代表右边的表为主表，跟内连接区别就是，内连接必须两张表同时存在的相同product_id才能查询出来，而右外连接会把只有右边表才存在的product_id也查出来）

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name,
P.sale_price
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P
ON SP.product_id = P.product_id;