本文是个人学习笔记学习素材参考《SQL必知必会第5版》仅用于个人学习记录非商用。样例数据、练习思路来自该书。十.分组数据10.1 数据分组目前为止所有计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的如果想要返回每个供应商提供的产品数目或者返回只提供一项产品的供应商产品或者返回提供10个以上产品的供应商的产品就要用到分组使用分组可以将数据分为多个逻辑组对每个组进行聚集计算。10.2 创建分组使用的是GROUP BY子句SELECT VEND_ID,COUNT(*) AS NUM_PRODSFROM PRODUCTSGROUP BY VEND_ID;GROUP BY的规定可以包含任意数目的列因而可以对分组进行嵌套更细致地进行数据分组如果在GROUP BY子句中嵌套了分组数据将在最后指定的分组上进行汇总GROUP BY字句中列出的每一列都必须是检索列或有效的表达式但不能是聚集函数大多是SQL实现不允许GROUP BY列带有长度可变的数据类型除聚类计算语句外SELECT语句中的每一列都必须在GROUP BY子句中给出如果分组列中包含具有NULL值的行则NULL将作为一个分组返回GROUP BY字句必须出现在WHERE子句之后ORDER BY子句之前10.3 过滤分组HAVING非常类似于WHEREWHERE过滤行HAVING过滤分组SELECT CUST_ID, COUNT(*) AS ORDERSFROM ORDERSGROUP BY CUST_IDHAVINGCOUNT(*) 2;WHERE在数据分组前进行过滤HAVING在数据分组后进行过滤例列出具有两个以上产品且其价格大于等于4的供应商SELECT VEND_ID, COUNT(*) AS NUM_PRODSFROM PRODUCTSWHEREPROD_PRICE 4GROUP BY VEND_IDHAVINGCOUNT(*) 2;10.4 分组和排序ORDER BY与GROUP BYORDER BYGROUP BY对产生的输出排序对行分组但输出可能不是分组的顺序任意列都可以使用只可能使用选择列或表达式列而且必须使用每个选择列表达式不一定需要如果与聚类函数一起使用列或表达式则必须使用一般在使用GROUP BY子句时应该也给出ORDER BY子句千万不要仅依赖GROUP BY排序数据例检索包含三个或更多物品的订单号和订购物品的数目SELECT ORDER_NUM, COUNT(*) AS ITEMSFROM ORDERITEMSGROUP BY ORDER_NUMHAVING COUNT(*) 3;要按订购物品的数目排序输出需要添加ORDER BY子句SELECT ORDER_NUM, COUNT(*) AS ITEMSFROM ORDERITEMSGROUP BY ORDER_NUMHAVING COUNT(*) 3ORDER BYITEMS, ORDER_NUM;10.5 SELECT子句顺序SELECT子句顺序子句说明是否必须使用SELECT要返回的列或表达式是FROM从中检索数据的表仅在从表选择数据时使用WHERE行级过滤否GROUP BY分组说明仅在按组计算聚集时使用HAVING组级过滤否ORDER BY输出排序顺序否十一.使用子查询11.1 子查询查询任何SQL语句都是查询但此术语一般指SELECT语句SQL允许创建子查询即嵌套在其他查询中的查询11.2 利用子查询进行过滤例需要列出订购物品RGAN01的所有用户1检索包含物品RGAN01的所有订单的编号2检索具有前一步骤列出的订单编号的所有顾客ID3检索前一步骤返回的所有顾客ID的顾客信息先做1查询出物品GRAN01的所有订单编号SELECT ORDER_NUMFROM ORDERITEMSWHERE PROD_ID RGAN01;然后做2找出所有顾客IDSELECT CUST_IDFROM ORDERSWHERE ORDER_NUM IN (20007,20008);结合这两个查询SELECT CUST_IDFROM ORDERSWHERE ORDER_NUM IN(SELECT ORDER_NUMFROM ORDERITEMSWHERE PROD_ID RGAN01);在SELECT语句中子查询总是从内向外处理格式化SQL把子查询分解为多行并进行适当的缩进能极大地简化子查询的作用接下来做3SELECT CUST_NAME, CUST_CONTACTFROM CUSTOMERSWHERE CUST_ID IN (1000000004,1000000005);把三个步骤合并起来SELECT CUST_NAME, CUST_CONTACTFROM CUSTOMERSWHERE CUST_ID IN (SELECT CUST_IDFROM ORDERSWHERE ORDER_NUM IN (SELECT ORDER_NUMFROM ORDERITEMSWHERE PROD_ID RGAN01));在实际使用时由于性能的限制不能嵌套太多的子查询注意作为子查询的SELECT语句只能查询单个列查询多个列将返回错误11.3 作为计算字段使用子查询使用子查询的另一方法是创建计算字段例需要显示customers表中每个顾客的订单总数订单与相应的顾客ID储存在orders表中1从customers表中检索顾客列表2对于检索出的每个顾客统计其在orders表中的订单数目如果对顾客1000000001的订单进行计数SELECT COUNT(*) AS ORDERSFROM ORDERSWHERE CUST_ID 1000000001;要对每个顾客执行COUNT(*)应该将它作为一个子查询SELECT CUST_NAME, CUST_STATE,(SELECT COUNT(*)FROM ORDERSWHERE ORDERS.CUST_ID CUSTOMERS.CUST_ID)AS ORDERSFROM CUSTOMERSORDER BY CUST_NAME;