子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。
例如,SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2)
其中SELECT * FROM t1 WHERE col1,称为Out Query/Out Statement
SELECT col2 FROM t2,称为Subquery。
子查询必须出现在圆括号之间。
使用比较运算符的子查询
比较运算符:=,<,>,<=,>=,<>,!=,<=>
-- 求所有电脑产品的平均价格,并且保留两位小数(AVG,MAX,MIN,COUNT,SUM为聚合函数)
SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;
-- 查询所有价格大于平均价格的商品,并且按价格降序排序
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > 5845.10 ORDER BY goods_price DESC;
-- 使用子查询来实现
SELECT goods_id,goods_name,goods_price FROM tdb_goods
WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods)
-- 查询类型为“超记本”的商品价格
SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本';
-- 查询价格大于或等于"超级本"价格的商品,并且按价格降序排列
SELECT goods_id,goods_name,goods_price FROM tdb_goods
WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')
ORDER BY goods_price DESC;
-- = ANY 或 = SOME 等价于 IN
SELECT goods_id,goods_name,goods_price FROM tdb_goods
WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')
ORDER BY goods_price DESC;