嵌套的 distinc
select avg(distinct prod_price) as avg_price from products; 10.0 分组 10.1 创建分组 group by select 列名 from 表名 group by 列名; select vend_id, count(*) as nums_prods from products group by vend_id;关于 group by 的使用说明
group by 子句可以包含任意数目的列
如果在group by子句中嵌套了分组,数据将在最后规定的分组上进行分组
group by 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚合函数)
可以这样想即 select 列1 ... group by 列1 注意不能 group by 别名
如果列中含有 NULL 值，则分组单独显示出来，有多个Null，它们将分为一组
group by 子句必须出现在 where 子句后，order by 子句之前
使用 with rollup 关键字，可以得到每个分组以及每个分组汇总级别的值
-- 返回单独的vend_id供货总数 select count(vend_id) from products where vend_id = '1001'; +----------------+ | count(vend_id) | +----------------+ | 3 | +----------------+ 1 row in set (0.00 sec) -- 返回每个vend_id供货总数 select vend_id, count(*) as nums_prods from products group by vend_id with rollup; +---------+------------+ | vend_id | nums_prods | +---------+------------+ | 1001 | 3 | | 1002 | 2 | | 1003 | 7 | | 1005 | 2 | | NULL | 14 | +---------+------------+ 5 rows in set (0.00 sec) 10.1 过滤分组 having注意 where 为过滤行，但是 having 支持 where 的所有操作
select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2; SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2; 10.2 分组和排序检索总订单价格大于50的订单号和总计订单价格在按照总计订单价格排序输出
SELECT order_num, SUM( quantity * item_price ) AS order_total FROM orderitems GROUP BY order_num HAVING order_total >= 50 ORDER BY order_total; SELECT order_num, SUM( quantity * item_price ) AS order_total FROM orderitems GROUP BY order_num HAVING order_total >= 50 ORDER BY order_total LIMIT 2; 11.0 使用子查询子查询：即嵌套在其它查询中的查询
现在需要列出订购物品TNT2的所有客户
检索包含物品TNT2的所有订单的编号
检索具有前一步骤列出的订单编号的所有客户ID
检索前一步骤返回的所有客户ID的客户信息
-- 1） select order_num from orderitems where prod_id = 'TNT2'; +-----------+ | order_num | +-----------+ | 20005 | | 20007 | +-----------+ 2 rows in set (0.00 sec) -- 2) select cust_id from orders where order_num in (20005, 20007); +---------+ | cust_id | +---------+ | 10001 | | 10004 | +---------+ 2 rows in set (0.00 sec) -- 3） 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 | +----------------+--------------+ 2 rows in set (0.01 sec)现在呢，我们就可以把这三个查询合并为一个查询
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' ));像上面的语句可能不是最有效率的方式，可以在后面参考（联结表）
11.1 作为计算字段使用子查询假如需要显示customers表中每个客户的总订单数。 订单与相应的客户ID存储在orders表中
从customers表中检索客户列表
对于检索出来的每个客户，统计其在orders表中的订单数目
SELECT cust_name, cust_state, ( SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id ) AS orders FROM customers ORDER BY cust_name; 12.0 联结表Mysql联结的表越多会导致性能的下降
12.1 创建联结表 -- 等值联结 SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;如果上面的句子删除掉 where 就会出现 笛卡尔积，这并非是我们需要的数据
-- 笛卡尔积 SELECT vend_name, prod_name, prod_price FROM vendors, products ORDER BY vend_name, prod_name; 12.2 内部联结此联结与上面的等值接连一致,唯一不同的是这里使用关键子 inner join ... on 进行限定
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id; 12.3 联结多个表 SELECT vend_name, prod_name, quantity, prod_price FROM vendors, products, orderitems WHERE vendors.vend_id = products.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;