神奇的 SQL 之子查询( 二 ) _小知识

现在我们要实现如下要求：统计出各个类别下商品的数量，我们可以写出如下 SQL
-- 最容易想到的 GROUP BYSELECT category, COUNT(*) cnt FROM t_commodityGROUP BY category-- 子查询实现，这里貌似多此一举，权且当做一个示例-- 普通子查询一般应用于多表之间的查询，比如学生表、课程表、选课表之间的一些查询SELECT * FROM( SELECT category, COUNT(*) cnt FROM t_commodity GROUP BY category) cs;标量子查询普通子查询一般是返回多行结果（偶尔也会只返回 1 行，有时也会查不到结果）；当返回结果是 1 行 1 列时，该子查询被称作标量子查询，标量子查询有个特殊的限制，必须而且只能返回 1 行 1 列的结果。
说的简单点：标量子查询就是返回单一值的子查询。由于返回值是单一值，所以标量子查询可以用在 = 或 <> 这样需要单一值的比较运算符之中，这也正是其优势所在。我们来看一些简单的例子，还是以 t_commodity 为例，假设我们有如下需求，我们该如何实现它
1、查询出售单价高于平均出售单价的商品
2、查询所有商品信息，并在每个商品的信息中加入平均出售单价、平均进货单价
3、按照商品类别分类，查询出平均出售单价高于全部商品的平均出售单价的商品类别（类别名、类别平均出售单价）
查询 1
第一感觉，我们也许会写出如下的 SQL
-- 错误的 SQLSELECT * FROM t_commodityWHERE sell_unit_price > AVG(sell_unit_price);实际上这个 SQL 执行会报错，WHERE 子句中不能使用聚合函数。那这个查询要怎么写了，此时标量子查询就派上用场了，SQL 如下
-- 查询出售单价高于平均出售单价的商品SELECT * FROM t_commodityWHERE sell_unit_price > ( SELECT AVG(sell_unit_price)FROM t_commodity);查询 2
这个 SQL 应该比较容易想到，SELECT 子句中加入平均出售单价、平均进货单价列即可，如下
-- 查询所有商品信息，并在每个商品的信息中加入平均出售单价、平均进货单价SELECT *, (SELECT AVG(sell_unit_price) FROM t_commodity) avg_sell_price, (SELECT AVG(purchase_unit_price) FROM t_commodity) avg_purchase_priceFROM t_commodity;查询 3
先以类别进行分组，然后取分组后各个类别的平均出售价格，与全部商品的平均出售价格比较，过滤出满足条件的类别，SQL 如下
-- 按照商品类别分类，查询出平均出售单价高于全部商品的平均出售单价的商品类别（类别名、类别平均出售单价）SELECT category, AVG(sell_unit_price) category_avg_sell_priceFROM t_commodityGROUP BY categoryHAVING AVG(sell_unit_price) > ( SELECT AVG(sell_unit_price)FROM t_commodity)

文章插图

使用标量子查询时，我们需要注意一点：我们要明确的知道该子查询返回的结果就是单一值，绝对不能返回多行结果。不然执行会报错
关联子查询关联子查询是指一个包含对表的引用的子查询，该表也显示在外部查询中。通俗一点来讲，就是子查询引用到了主查询的数据数据。在关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。另外，在关联子查询中是信息流是双向的，外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策。光看概念，晦涩难懂，我们结合具体的例子来看关联子查询
还是以商品表：t_commodity 为例，如何选取出各商品类别中高于该类别平均出售价格的商品，可能大家还没明白这个需求，那么我们具体点
所有商品的类别、出售价格如下

文章插图

各类别及类别平均出售价格如下

文章插图

我们得到的正确结果应该是

文章插图

这个 SQL 我们要如何写？像这样
-- 错误的 SQLSELECT * FROM t_commodityWHERE sell_unit_price > ( SELECT AVG(sell_unit_price) FROM t_commodity GROUP BY category)是肯定不行的，那正确的打开方式应该是怎么样的了，此时需要关联子查询上场了，SQL如下
SELECT * FROM t_commodity t1WHERE sell_unit_price > ( SELECT AVG(sell_unit_price) FROM t_commodity t2 WHERE t1.category = t2.category GROUP BY category)