mysql groupby having_mysql group by having的使用方法

表格构

+-------------------+---------------+------+-----+------------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------------------+---------------+------+-----+------------+----------------+

| order_id | int(11) | NO | PRI | NULL | auto_increment |

| order_date | date | NO | MUL | 0000-00-00 | |

| status | tinyint(4) | NO | MUL | 0 | |

| customer_id | int | NO | MUL | 0 | |

| first_name | varchar(50) | NO | | NULL | |

| last_name | varchar(50) | NO | | NULL | |

| email_address | varchar(100) | NO | | NULL | |

| country_code | char(2) | NO | MUL | NULL | |

| order_total | decimal(10,2) | NO | | 0.00 | |

+-------------------+---------------+------+-----+------------+----------------+

如果你想得到一个国家的订单计数，你会运行一个这样的查询：如果你想得到一个由国家的订单计数，你会运行这样的查询：

SELECT country_code, COUNT(*)

FROM orders_header

GROUP BY country_code

从结果集的前几行可能看起来像这样：

+--------------+----------+

| country_code | count(*) |

+--------------+----------+

| AE | 18 |

| AR | 18 |

| AS | 45 |

| AT | 54 |

| AU | 1277 |

| AZ | 1 |

...

如果你想找出所有的国家只有一个订单，你可以把“group by ”和“having”这样的：

SELECT country_code, COUNT(*)

FROM orders_header

GROUP BY country_code

HAVING COUNT(*) = 1

这将只返回国家的代码，找出总数为1的，像这样：

+--------------+----------+

| country_code | COUNT(*) |

+--------------+----------+

| AZ | 1 |

| BH | 1 |

| GH | 1 |

| KG | 1 |

| KW | 1 |

| MD | 1 |

| NP | 1 |

| QA | 1 |

| WF | 1 |

| ZW | 1 |

+--------------+----------+

10 rows in set (0.18 sec)

作为另一个例子，如果你想找出所有的客户已经放置了超过5个订单，你可以使用相同的排序这样的查询：

SELECT customer_id, COUNT(*)

FROM orders_header

GROUP BY customer_id

HAVING COUNT(*) > 5

如果你想找到那些已经花费了超过1000美元的的订单

SELECT customer_id, SUM(order_total)

FROM orders_header

GROUP BY email_address

HAVING SUM(order_total) > 1000;