【转】《与MySQL的零距离接触》第五章：子查询与连接 （5-3：由比较运算符引发的子查询）

转载出处：

慕课网：《与MySQL的零距离接触》笔记目录https://zhangjia.tv/682.html

5-3：由比较运算符引发的子查询

一. 使用比较运算符的子查询

主要有：=、>、<、>=、<=、<>、!=、<=>

语法结构：

operand comparison_operator subquery

比如查找所有商品的平均价格（需要用到聚合函数AVG）：

mysql> SELECT AVG(goods_price) FROM tdb_goods;
+------------------+
| AVG(goods_price) |
+------------------+
|     5636.3636364 |
+------------------+
1 row in set (0.01 sec)

对上面的平均价格进行四舍五入，并保留2位小数（需要用到聚合函数ROUND）：

mysql> SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
+---------------------------+
| ROUND(AVG(goods_price),2) |
+---------------------------+
|                   5636.36 |
+---------------------------+
1 row in set (0.01 sec)

查看大于平均价格的商品：

mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods) \G
*************************** 1. row ***************************
   goods_id: 3
 goods_name: G150TH 15.6英寸游戏本
goods_price: 8499.000
*************************** 2. row ***************************
   goods_id: 7
 goods_name: SVP13226SCB 13.3英寸触控超极本
goods_price: 7999.000
*************************** 3. row ***************************
   goods_id: 13
 goods_name: iMac ME086CH/A 21.5英寸一体电脑
goods_price: 9188.000
*************************** 4. row ***************************
   goods_id: 17
 goods_name: Mac Pro MD878CH/A 专业级台式电脑
goods_price: 28888.000
*************************** 5. row ***************************
   goods_id: 18
 goods_name:  HMZ-T3W 头戴显示设备
goods_price: 6999.000
*************************** 6. row ***************************
   goods_id: 20
 goods_name: X3250 M4机架式服务器 2583i14
goods_price: 6888.000
*************************** 7. row ***************************
   goods_id: 21
 goods_name:  HMZ-T3W 头戴显示设备
goods_price: 6999.000
7 rows in set (0.01 sec)

查找超级本类型的价格：

mysql> SELECT goods_name,goods_price FROM tdb_goods WHERE goods_cate = '超级本';
+---------------------------------+-------------+
| goods_name                      | goods_price |
+---------------------------------+-------------+
| X240(20ALA0EYCD) 12.5英寸超极本        |    4999.000 |
| U330P 13.3英寸超极本                   |    4299.000 |
| SVP13226SCB 13.3英寸触控超极本            |    7999.000 |
+---------------------------------+-------------+
3 rows in set (0.00 sec)

二. 用ANY、SOME 或ALL 修饰的比较运算符

如果子查询返回多个结果就需要用到ANY、SOME和ALL，ANY和SOME等价，都是指的符合其中一个的意思，ALL需要是符合全部

operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)

使用ANY、SOME和ALL的原则：

比如查看一下>=超级本中最贵的的价格的商品：

mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate ='超级本');
+----------+----------------------------------+-------------+
| goods_id | goods_name                       | goods_price |
+----------+----------------------------------+-------------+
|        3 | G150TH 15.6英寸游戏本                    |    8499.000 |
|        7 | SVP13226SCB 13.3英寸触控超极本             |    7999.000 |
|       13 | iMac ME086CH/A 21.5英寸一体电脑            |    9188.000 |
|       17 | Mac Pro MD878CH/A 专业级台式电脑           |   28888.000 |
+----------+----------------------------------+-------------+
4 rows in set (0.00 sec)

可以看到显示的所有商品价格都>=三个超级本中价格，实际上也就是大于7999元，所以ALL在= 处会什么也查询不到，因为没有商品即等于4299又等于4999又等于7999

而ANY和SOME的=返回的是任意值，也就是返回和超级本中的三个商品价格任意相等的，在本例中即是它们本身：

mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate ='超级本');
+----------+---------------------------------+-------------+
| goods_id | goods_name                      | goods_price |
+----------+---------------------------------+-------------+
|        5 | X240(20ALA0EYCD) 12.5英寸超极本        |    4999.000 |
|        6 | U330P 13.3英寸超极本                   |    4299.000 |
|        7 | SVP13226SCB 13.3英寸触控超极本            |    7999.000 |
+----------+---------------------------------+-------------+
3 rows in set (0.00 sec)