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表中的订单总数。
必须注意限定是有歧义的列名。

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