表格构
+-------------------+---------------+------+-----+------------+----------------+
| 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;