MySQL分组数据与子查询

mysql> SELECT SUM(item_price * quantity) AS total_price FROM orderitems WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
|      149.87 |
+-------------+

mysql> SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+

mysql> SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+

13章 分组数据

mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

排序

mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id ORDER BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

使用 ROLLUP

mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
|    NULL |        14 |
+---------+-----------+

13.3 过滤分组

mysql> SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+

mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >= 2 ORDER BY num_prods;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1002 |         2 |
|    1005 |         2 |
|    1001 |         3 |
|    1003 |         7 |
+---------+-----------+

mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2 ORDER BY num_prods;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1005 |         2 |
|    1003 |         4 |
+---------+-----------+

13.4 分组和排序

mysql> SELECT order_num, SUM(quantity * item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
+-----------+------------+

HAVING 可以使用AS后的描述

mysql> SELECT order_num, SUM(quantity * item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING ordertotal >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
+-----------+------------+

使用ORDER BY 进行排序

mysql> SELECT order_num, SUM(quantity * item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING ordertotal >= 50 ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+

13.5 SELECT 子句顺序

14章 使用子查询

利用子查询进行过滤
1.

mysql> SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
同效果： mysql> SELECT order_num FROM orderitems WHERE prod_id LIKE 'TNT2';

+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+

2.

mysql> SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+

3.

mysql> SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (10001, 10004);
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

合并1.， 2. and 3.

mysql> SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2')); 
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

作为计算字段使用子查询

mysql> SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      2 |
| E Fudd         | IL         |      1 |
| Mouse House    | OH         |      0 |
| Wascals        | IN         |      1 |
| Yosemite Place | AZ         |      1 |
+----------------+------------+--------+

使用了完全限定列名。 orders.cust_id = customers.cust_id代表让SQL比较orders表中的cust_id 与当前正从customers表中检索中的cust_id.若不使用完全限定列名：

mysql> SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = cust_id) AS orders FROM customers ORDER BY cust_name;
+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      5 |
| E Fudd         | IL         |      5 |
| Mouse House    | OH         |      5 |
| Wascals        | IN         |      5 |
| Yosemite Place | AZ         |      5 |
+----------------+------------+--------+

此时，MySQL将假定对 orders表中的cust_id自身进行查询。而SELECT COUNT(*) FROM orders WHERE orders.cust_id = cust_id总是返回orders表中的订单总数。
必须注意限定是有歧义的列名。

相关子查询：涉及外部查询的子查询。