SQL 分组数据
一、创建分组：
-
分组是使用SELECT语句的
GROUP BY子句建立的。 -
下面举个栗子：
mysql> SELECT * FROM products; +---------+---------+------------+------------+--------------------------------+ | prod_id | vend_id | prod_name | prod_price | prod_desc | +---------+---------+------------+------------+--------------------------------+ | BNBG01 | DLL01 | Fish bean | 3.49 | Fish bean bean bag worms with | | BNBG02 | DLL01 | Bird bean | 3.49 | Bird beanincluded | | BNBG03 | DLL01 | Rabbit bey | 3.49 | Rabbit bebean bag carrots | | BR01 | BRS01 | 8 inch te | 5.99 | 8 inch tep and jacket | | BR02 | BRS01 | 12 inch t | 8.99 | 12 inch tap and jacket | | BR03 | BRS01 | 18 inch t | 11.99 | 18 inch tap and jacket | | RGAN01 | DLL01 | Raggedy A | 4.99 | 18 inch R | | RYL01 | FNG01 | King doll | 9.49 | 12 inch kments and crown | | RYL02 | FNG01 | Queen dol | 9.49 | 12 inch qrments and crown | +---------+---------+------------+------------+--------------------------------- -
使用
GROUP BY子句：SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;+---------+-----------+ | vend_id | num_prods | +---------+-----------+ | BRS01 | 3 | | DLL01 | 4 | | FNG01 | 2 | +---------+-----------+ 3 rows in set (0.00 sec)-
SELECT 语句指定了两个列：
1.vend_id 包含产品供应商的ID。
2.num_prods为计算字段(用COUNT(*)函数建立)
-
GROUP BY子句指示DBMS按vend_id排序并分组数据。这就会对每个vend_id而不是整个表计算num_prods一次。 -
因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成，GROUP BY 子句指示DBMS分组数据，然后对每个组而不是整个结果进行聚集。
-
-
注意：在使用GROUP BY 子句前，需要知道一些重要的规定。
GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致进行数据的分组。如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。- 也就是说在建立分组时，指定的所有列都一起计算(所以不能从个别的列取回数据)
GROUP BY子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚合函数)。如果在SELECT 中指定相同的表达式。不能使用别名。大多数SQL实现不允许GROUP BY列带有长度可变的数据类型(如文本或备注型字段)。除聚集计算语句外，SELECT 语句中的每一列都必须在GROUP BY子句中给出。如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。GROUP BY 子句必须出现在WHERE子句之后，ORDER BY 之前。
-
注意：通过相对位置指定列
- 有的SQL实现允许根据SELECT列表中的位置指定GROUP BY的列。
- 举个栗子：
- GROUP BY 2, 1可表示按选择的第二个列分组，然后再按第一个列分组。虽然这种记法很方便，
但并非所有SQL实现都支持，并且使用它容易再编辑SQL语句时出错。
- GROUP BY 2, 1可表示按选择的第二个列分组，然后再按第一个列分组。虽然这种记法很方便，
二、过滤分组：
-
除了能用GROUP BY 分组数据外，
SQL还允许过滤分组，规定包括哪些分组，排除哪些分组。 -
可能我们会想到使用WHERE子句进行过滤。而实际上WHERE过滤指定的是行而不是分组。
事实上，WHERE没有分组的概念。 -
下面我们介绍
HAVING子句，HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是，WHERE过滤行，而HAVING过滤分组。 -
注意：HAVING支持所有WHERE操作符
- 以前写的这些有关WHERE的所有技术和选项都适合于HAVING。它们的句法是相同的，只是关键字有差别。
-
首先看一下这个Orders表都有哪些数据：
mysql> SELECT * FROM orders; +-----------+---------------------+------------+ | order_num | order_date | cust_id | +-----------+---------------------+------------+ | 20005 | 2012-05-01 00:00:00 | 1000000001 | | 20006 | 2012-01-12 00:00:00 | 1000000003 | | 20007 | 2012-01-30 00:00:00 | 1000000004 | | 20008 | 2012-02-03 00:00:00 | 1000000005 | | 20009 | 2012-02-08 00:00:00 | 1000000001 | +-----------+---------------------+------------+ 5 rows in set (0.02 sec) -
使用HAVING子句过滤分组：
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;+------------+--------+ | cust_id | orders | +------------+--------+ | 1000000001 | 2 | +------------+--------+ 1 row in set (0.00 sec) -
可以尝试使用WHERE子句进行过滤，可以发现WHERE子句根本就不起作用，
因为过滤是基于分组聚集值，而不是特定行的值。 -
说明：HAVING和WHERE的差别
- 这里有另一种理解方法，
WHERE再数据分组前进行过滤，HAVING再数据分组后进行过滤。 - WHERE排除的行不包括再分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。
- 这里有另一种理解方法，
-
用WHERE和HAVING子句再一条语句出现：
-
举个栗子：它列出具有两个以上产品且其价格大于等于4的供应商。
-
先查看一下products表中有哪些数据：
mysql> SELECT * FROM products; +---------+---------+------------+------------+--------------------------------+ | prod_id | vend_id | prod_name | prod_price | prod_desc | +---------+---------+------------+------------+--------------------------------+ | BNBG01 | DLL01 | Fish bean | 3.49 | Fish bean bean bag worms with | | BNBG02 | DLL01 | Bird bean | 3.49 | Bird beanincluded | | BNBG03 | DLL01 | Rabbit bey | 3.49 | Rabbit bebean bag carrots | | BR01 | BRS01 | 8 inch te | 5.99 | 8 inch tep and jacket | | BR02 | BRS01 | 12 inch t | 8.99 | 12 inch tap and jacket | | BR03 | BRS01 | 18 inch t | 11.99 | 18 inch tap and jacket | | RGAN01 | DLL01 | Raggedy A | 4.99 | 18 inch R | | RYL01 | FNG01 | King doll | 9.49 | 12 inch kments and crown | | RYL02 | FNG01 | Queen dol | 9.49 | 12 inch qrments and crown | +---------+---------+------------+------------+---------------------------------SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;+---------+-----------+ | vend_id | num_prods | +---------+-----------+ | BRS01 | 3 | | FNG01 | 2 | +---------+-----------+ 2 rows in set (0.00 sec) -
如果没有WHERE子句，就会多检索出一行。
SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 4 GROUP BY vend_id;+---------+-----------+ | vend_id | num_prods | +---------+-----------+ | BRS01 | 3 | | DLL01 | 1 | | FNG01 | 2 | +---------+-----------+ 3 rows in set (0.00 sec)
-
-
注意：使用HAVING和WHERE
- HAVING与WHERE非常类似，如果不指定GROUP BY， 则大多数DBMS会同等对待它们。不过，要能区分这一点。
使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。
- HAVING与WHERE非常类似，如果不指定GROUP BY， 则大多数DBMS会同等对待它们。不过，要能区分这一点。
三、分组和排序：
-
GROUP BY 和 ORDER BY 经尝完成相同的工作，但它们非常不同，下面列出它们之间的差别：
ORDER BY GROUP BY 对产生的输出排序 对行的分组，但输出可能不是分组 任意列都可以使用(甚至非选择的列也可以使用) 只可能使用选择列或表达式列，而且必须使用每个选择列表达式 不一定需要 如果与聚集函数一起使用列(或表达式)，则必须使用 - 第一项差别极为重要。我们经尝发现，用GROUP BY 分组的数据确实是以分组顺序输出的。但并不总这样，这不是SQL规范所要求的。即使特定DBMS总是按给出的GROUP BY子句排序数据，用户也可能会要求以不同的顺序排序。就因为你以某种方式分组数据，并不表示你需要以相同的方式排序输出。
应该提供明确的ORDER BY子句，即使效果等同于GROUP BY子句。
- 第一项差别极为重要。我们经尝发现，用GROUP BY 分组的数据确实是以分组顺序输出的。但并不总这样，这不是SQL规范所要求的。即使特定DBMS总是按给出的GROUP BY子句排序数据，用户也可能会要求以不同的顺序排序。就因为你以某种方式分组数据，并不表示你需要以相同的方式排序输出。
-
注意：不要忘记ORDER BY
一般再使用GROUP BY 子句时，应该也给出ORDER BY 子句。这是保证数据正确排序的唯一方法。
-
为说明GROUP BY 和 ORDER BY的使用方法，举个栗子：
-
它检索包含三个或更多物品的订单号和订购物品的数目：
-
首先看一下要查询的orderitems表中都有哪些数据：
mysql> SELECT * FROM orderitems; +-----------+------------+---------+----------+------------+ | order_num | order_item | prod_id | quantity | item_price | +-----------+------------+---------+----------+------------+ | 20005 | 1 | BR01 | 100 | 5.49 | | 20005 | 2 | BR03 | 100 | 10.99 | | 20006 | 1 | BR01 | 20 | 5.99 | | 20006 | 2 | BR02 | 10 | 8.99 | | 20006 | 3 | BR03 | 10 | 11.99 | | 20007 | 1 | BR03 | 50 | 11.49 | | 20007 | 2 | BNBG01 | 100 | 2.99 | | 20007 | 3 | BNBG02 | 100 | 2.99 | | 20007 | 4 | BNBG03 | 100 | 2.99 | | 20007 | 5 | RGAN01 | 50 | 4.49 | | 20008 | 1 | RGAN01 | 5 | 4.99 | | 20008 | 2 | BR03 | 5 | 11.99 | | 20008 | 3 | BNBG01 | 10 | 3.49 | | 20008 | 4 | BNBG02 | 10 | 3.49 | | 20008 | 5 | BNBG03 | 10 | 3.49 | | 20009 | 1 | BNBG01 | 250 | 2.49 | | 20009 | 2 | BNBG02 | 250 | 2.49 | | 20009 | 3 | BNBG03 | 250 | 2.49 | +-----------+------------+---------+----------+------------+ 18 rows in set (0.00 sec)mysql> SELECT order_num, COUNT(*) AS cur_orders FROM orderitems GROUP BY order_num HAVING COUNT(*) >= 3;+-----------+------------+ | order_num | cur_orders | +-----------+------------+ | 20006 | 3 | | 20007 | 5 | | 20008 | 5 | | 20009 | 3 | +-----------+------------+ 4 rows in set (0.00 sec) -
要按订购物品的数目排序输出(数目做降序)，需要添加ORDER BY子句：
SELECT order_num, COUNT(*) AS items FROM orderitems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num DESC;+-----------+-------+ | order_num | items | +-----------+-------+ | 20009 | 3 | | 20006 | 3 | | 20008 | 5 | | 20007 | 5 | +-----------+-------+ 4 rows in set (0.00 sec)
-
四、SELECT 子句顺序：
-
回顾一下SELECT语句中子句的顺序，下面表以SELECT语句中使用时必须遵循的次序。
子句 说明 是否必须使用 SELECT 要返回的列或表达式 是 FROM 从中检索数据的表 仅再从表选择数据时使用 WHERE 行级过滤 否 GROUP BY 分组说明 仅在按组计算聚集时使用 HAVING 组级过滤 否 ORDER BY 输出排序顺序 否
扫一扫
511
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
