Mysql分组函数

HAVING 和 WHERE 区别

  • HAVING 过滤分组数据
  • HWERE 过滤行数据
  • HAVING 数据分组后进行过滤,WHERE 数据分组前进行过滤(重要区别)
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select vend_id, count(*) as num_prods, prod_price 
from products where
prod_price >= 10
group by vend_id
having count(*) >= 2;

+---------+-----------+------------+
| vend_id | num_prods | prod_price |
+---------+-----------+------------+
| 1003 | 4 | 13.00 |
| 1005 | 2 | 35.00 |
+---------+-----------+------------+
2 rows in set (0.02 sec)

查询订单总价大于50美元的订单 且 按照价格升序排列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select order_num, sum(item_price*quantity) as sum 
from orderitems
group by order_num
having sum >= 50
order by sum;

+-----------+---------+
| order_num | sum |
+-----------+---------+
| 20006 | 55.00 |
| 20008 | 125.00 |
| 20005 | 149.87 |
| 20007 | 1000.00 |
+-----------+---------+
4 rows in set (0.05 sec)