常用的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;