要求:在表 orders 中找到订单数最多客户对应的 customer_number。
数据保证订单数最多的顾客恰好只有一位。
orders表的结构:
| Column | Type |
|-------------------|-----------|
| order_number (PK) | int |
| customer_number | int |
| order_date | date |
| required_date | date |
| shipped_date | date |
| status | char(15) |
| comment | char(200) |
orders表:
| order_number | customer_number | order_date | required_date | shipped_date | status | comment |
|--------------|-----------------|------------|---------------|--------------|--------|---------|
| 1 | 1 | 2017-04-09 | 2017-04-13 | 2017-04-12 | Closed | |
| 2 | 2 | 2017-04-15 | 2017-04-20 | 2017-04-18 | Closed | |
| 3 | 3 | 2017-04-16 | 2017-04-25 | 2017-04-20 | Closed | |
| 4 | 3 | 2017-04-18 | 2017-04-28 | 2017-04-25 | Closed | |
Result Table:
| customer_number |
|-----------------|
| 3 |
分析:
1.要求出订单数最多的customer_number,通过customer_number分组
,将count(customer_number)降序排序,取第一个customer_number即可
SQL语句:
select customer_number
from orders
group by customer_number
order by count(customer_number) desc
limit 1;