Home > Database > Mysql Tutorial > body text

MySQL学习足迹记录11--分组数据--GROUP BY,HAVING_MySQL

WBOY
Release: 2016-06-01 13:31:37
Original
910 people have browsed it

bitsCN.com

MySQL学习足迹记录11--分组数据--GROUP BY,HAVING

 

1.创建分组GROUP BY

   先列出所有的vend_id,以便作对比

 mysql> SELECT vend_id FROM products;+---------+| vend_id |+---------+|    1001 ||    1001 ||    1001 ||    1002 ||    1002 ||    1003 ||    1003 ||    1003 ||    1003 ||    1003 ||    1003 ||    1003 ||    1005 ||    1005 |+---------+14 rows in set (0.00 sec) 用GROUP BY进行分组mysql> SELECT vend_id,COUNT(*) AS num_prods         -> FROM products                  #先分组,再分别计算COUNT(*)         -> GROUP BY vend_id;+---------+-----------+| vend_id | num_prods |+---------+-----------+|    1001 |         3 ||    1002 |         2 ||    1003 |         7 ||    1005 |         2 |+---------+-----------+4 rows in set (0.00 sec)
Copy after login

TIPS:

*如果列中有多行NULL值,它们将分为一组

*GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前

2.过滤分组HAVING

*HAVING 与 WHERE 的区别:

WHERE过滤行,而HAVING过滤分组

 eg:   mysql> SELECT vend_id,COUNT(*) AS num_prods               -> FROM products            -> GROUP BY vend_id           -> HAVING COUNT(*)>2;        #从结果中过滤不符合COUNT(*)>2的组+---------+-----------+| vend_id | num_prods |+---------+-----------+|    1001 |         3 ||    1003 |         7 |+---------+-----------+2 rows in set (0.00 sec)
Copy after login

* WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤,所以,WHERE排除的行不包括在分组中

   eg:  先列出原始数据作对比mysql> SELECT vend_id,prod_price FROM products         -> ORDER BY prod_price;+---------+------------+| vend_id | prod_price |+---------+------------+|    1003 |       2.50 ||    1003 |       2.50 ||    1002 |       3.42 ||    1003 |       4.49 ||    1001 |       5.99 ||    1002 |       8.99 ||    1001 |       9.99 ||    1003 |      10.00 ||    1003 |      10.00 ||    1003 |      13.00 ||    1001 |      14.99 ||    1005 |      35.00 ||    1003 |      50.00 ||    1005 |      55.00 |+---------+------------+14 rows in set (0.00 sec)mysql> SELECT vend_id,COUNT(*) AS num_prods         -> FROM products         -> WHERE prod_price >14              #WHERE过滤后只剩下上表中最后3条记录,         -> GROUP BY vend_id                     #HAVING再过滤分组后vend_id为不符合COUNT(*) >=2组         -> HAVING COUNT(*) >=2;+---------+-----------+| vend_id | num_prods |+---------+-----------+|    1005 |         2 |+---------+-----------+1 row in set (0.00 sec)
Copy after login

3. 分组和排序

GROUP BY和ORDER BY的区别

*ORDER BY指定的条件可以是任意列

*GROUP BY指定的条件只可能使用选择列或列表达式

TIPS:

一般在使用GROUP BY子句时,也应该给出ORDER BY子句

Examples:

先列出原始数据:

 mysql> SELECT order_num,quantity,item_price FROM orderitems;+-----------+----------+------------+| order_num | quantity | item_price |+-----------+----------+------------+|     20005 |       10 |       5.99 ||     20005 |        3 |       9.99 ||     20005 |        5 |      10.00 ||     20005 |        1 |      10.00 ||     20006 |        1 |      55.00 ||     20007 |      100 |      10.00 ||     20008 |       50 |       2.50 ||     20009 |        1 |      10.00 ||     20009 |        1 |       8.99 ||     20009 |        1 |       4.49 ||     20009 |        1 |      14.99 |+-----------+----------+------------+11 rows in set (0.00 sec)mysql> SELECT order_num,SUM(quantity*item_price) AS ordertotal         -> FROM orderitems         -> GROUP BY order_num         -> HAVING SUM(quantity*item_price) >= 50;+-----------+------------+                    #未用ORDERBY指定排序,结果可能不是想要的,例如按ordertotal升序| order_num | ordertotal |+-----------+------------+|     20005 |     149.87 ||     20006 |      55.00 ||     20007 |    1000.00 ||     20008 |     125.00 |+-----------+------------+4 rows in set (0.00 sec)mysql> SELECT order_num,SUM(quantity*item_price) AS ordertotal         -> FROM orderitems                                       -> GROUP BY order_num                                  -> HAVING SUM(quantity*item_price) >=50         -> ORDER BY ordertotal;              # 用ORDERBY指定排序方式+-----------+------------+| order_num | ordertotal |+-----------+------------+|     20006 |      55.00 ||     20008 |     125.00 ||     20005 |     149.87 ||     20007 |    1000.00 |+-----------+------------+4 rows in set (0.01 sec)
Copy after login

 


bitsCN.com
Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template