SQL語句練習(xí)四

題目描述

在表 orders 中找到訂單數(shù)最多客戶對應(yīng)的 customer_number 。數(shù)據(jù)保證訂單數(shù)最多的顧客恰好只有一位。表 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) |

樣例輸入

| 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 |         |

樣例輸出

| customer_number |
|-----------------|
| 3               |

解釋 customer_number 為 '3' 的顧客有兩個(gè)訂單，比顧客 '1' 或者 '2' 都要多，因?yàn)樗麄冎挥幸粋€(gè)訂單 所以結(jié)果是該顧客的 customer_number ，也就是 3 。

解題思路

此題考查兩個(gè)知識點(diǎn)，一個(gè)是group，一個(gè)是limit。使用group針對customer_number做分組統(tǒng)計(jì)出訂單數(shù)最多的用戶。limit直接返回第一個(gè)，即為數(shù)量最多的用戶。

解題答案

// limit可以寫為limit 1;
select customer_number from orders  group by customer_number order by count(customer_number) desc limit 0,1;

進(jìn)階:如果存在多個(gè)用戶訂單數(shù)一樣多，如何返回對應(yīng)所有的customer_number.

SELECT
	customer_number
FROM
	orders o1
GROUP BY o1.customer_number
HAVING COUNT(*) = (
			SELECT
				COUNT(*)
			FROM
				orders o2
			GROUP BY o2. customer_number
			ORDER BY COUNT(*) DESC
			LIMIT 1
	);