SQL查询语句练习（二）

1. 找出供应商名称，所在城市

SELECT 公司名称 AS 供应商名称, 城市 AS 所在城市
FROM 供应商

---

2. 找出华北地区能够供应海鲜的所有供应商列表

SELECT 公司名称 AS 供应商列表
FROM 产品
LEFT JOIN 供应商 ON 供应商.`供应商ID` = 产品.`供应商ID`
LEFT JOIN 类别 ON 类别.`类别ID` = 产品.`类别ID`
WHERE 类别.类别ID = 8
AND 地区 = '华北'

---

3. 找出订单销售额前五的订单是经由哪家运货商运送的

SELECT 运货商.`公司名称`, ROUND((订单明细.单价 * 订单明细.数量 * (1 - 折扣)), 2) AS 销售额
FROM 订单明细
LEFT JOIN 订单 ON 订单明细.`订单ID` = 订单.`订单ID`
LEFT JOIN 运货商 ON 订单.`运货商` = 运货商.`运货商ID`
ORDER BY 销售额 DESC
LIMIT 5

---

4. 找出按箱包装的产品名称

SELECT 产品名称
FROM 产品
WHERE 单位数量 LIKE '%箱%'

---

5. 找出重庆的供应商能够供应的所有产品列表

SELECT 产品名称 
FROM 产品
LEFT JOIN 供应商 ON 产品.`供应商ID` = 供应商.`供应商ID`
WHERE 供应商.`城市` = '重庆'

---

6. 找出雇员郑建杰所有的订单并根据订单销售额排序

SELECT 订单明细.`订单ID`, ROUND((订单明细.单价 * 数量 * (1 - 折扣)), 2) AS 销售额
FROM 订单
LEFT JOIN 订单明细 ON 订单.`订单ID` = 订单明细.`订单ID`
LEFT JOIN 雇员 ON 订单.`雇员ID` = 雇员.`雇员ID`
WHERE 雇员.`姓氏` = '郑' 
AND 雇员.`名字` = '建杰'
ORDER BY 销售额 DESC

7. 建立产品与订单的关联

SELECT * 
FROM 订单
LEFT JOIN 订单明细 ON 订单.`订单ID` = 订单明细.`订单ID`
LEFT JOIN 产品 ON 订单明细.`产品ID` = 产品.`产品ID`

---

8. 找出订单10284的所有产品以及订单金额，运货商

SELECT 产品名称, ROUND((订单明细.`单价` * 数量 * (1 - `折扣`)),2) AS 订单金额, 公司名称
FROM 订单明细
LEFT JOIN 产品 ON 订单明细.`产品ID` = 产品.`产品ID`
LEFT JOIN 订单 ON 订单明细.`订单ID` = 订单.`订单ID`
LEFT JOIN 运货商 ON 订单.`运货商` = 运货商.`运货商ID`

---

9. 计算销量前10位的订单明细，结果集返回订单ID，订单日期，公司名称，发货日期，销售额，并排序

SELECT 订单.订单ID, 订购日期, 公司名称, 发货日期, ROUND((订单明细.单价 * 数量 * (1 - 折扣)), 2) AS 销售额
FROM  订单
LEFT JOIN 运货商 ON 订单.`运货商` = 运货商.`运货商ID`
LEFT JOIN 订单明细 ON 订单.`订单ID` = 订单明细.`订单ID`
ORDER BY 销售额 DESC
LIMIT 10

---

10. 按年度统计销售额

SELECT YEAR(订单.订购日期) AS 年度, SUM(ROUND((订单明细.单价 * 数量 * (1 - 折扣)),2)) AS 销售额
FROM 订单明细
LEFT JOIN 订单 ON 订单.`订单ID` = 订单明细.`订单ID`
GROUP BY 年度

---

11. 查询供应商中能够供应的产品样数最多的供应商

初步答案为：

SELECT 公司名称 AS 供应商, COUNT(产品名称) AS 产品样数
FROM 产品
LEFT JOIN 供应商 ON 产品.`供应商ID` = 供应商.`供应商ID`
GROUP BY 供应商
ORDER BY 产品样数 DESC
LIMIT 1

此处考虑通过MAX进行优化，但还未成功

---

12. 查询产品类别中包含的产品数量最多的类别

SELECT 类别名称, COUNT(产品.类别ID) AS 数量
FROM 类别,产品
WHERE 类别.`类别ID` = 产品.`类别ID`
GROUP BY 类别名称
ORDER BY 数量 DESC
LIMIT 1

---

13. 找出所有的订单中经由哪家运货商运货次数最多

SELECT 公司名称 AS 运货商, COUNT(运货商) AS 运货次数
FROM 订单, 运货商
WHERE 订单.`运货商` = 运货商.`运货商ID`
GROUP BY 运货商
ORDER BY 运货次数 DESC
LIMIT 1

---

14. 按类别，产品分组，统计销售额

SELECT 类别.类别名称, 产品.产品名称, SUM(ROUND((订单明细.单价 * 数量 * (1 - 折扣)),2)) AS 销售额
FROM 订单明细
LEFT JOIN 产品 ON 订单明细.`产品ID` = 产品.`产品ID`
LEFT JOIN 类别 ON 产品.`类别ID` = 类别.`类别ID`
GROUP BY 类别名称, 产品名称

---

15. 查询海鲜类别最大的一笔订单

SELECT 订单ID, 数量, 产品.产品名称, ROUND((订单明细.单价 * 数量 * (1 - 折扣)),2) AS 销售额
--
-- 最大理解为销售额最大
--
FROM 订单明细
LEFT JOIN 产品 ON 订单明细.`产品ID` = 产品.`产品ID`
LEFT JOIN 类别 ON 产品.`类别ID` = 类别.`类别ID`
WHERE 类别.`类别名称` = '海鲜'
ORDER BY 销售额 DESC
LIMIT 1

---

⭐️⭐️⭐️

16. 按季度统计销售量

本题在季度这里卡住了，经过查阅有以下两种方法。

• 方法一：

SELECT SUBSTRING(订单.订购日期, 1, 4) 年度, QUARTER(订单.订购日期) 季度, SUM(订单明细.数量) 订购数量     
FROM 订单
LEFT JOIN 订单明细 ON 订单明细.订单ID = 订单.订单ID     
GROUP BY 年度,季度     
ORDER BY 年度,季度

其中：
SUBSTRING(name, num1, num2)为字符串截取函数，截取name这个字段从第num1个字符开始，只截取之后的num2个字符（此处也可以通过YEAR()函数替代）。

QUARTER(date)返回年份日期的季度值，范围为1〜4。

• 方法二：

SELECT YEAR(订单.`订购日期`) AS 年度,
	(CASE
		WHEN MONTH(订单.`订购日期`) IN (1,2,3) THEN '1'
			WHEN MONTH(订单.`订购日期`) IN (4,5,6) THEN '2'
			WHEN MONTH(订单.`订购日期`) IN (7,8,9) THEN '3' ELSE '4' END) AS 季度,
SUM(订单明细.数量) AS 订购数量
FROM 订单
LEFT JOIN 订单明细 ON 订单明细.订单ID = 订单.订单ID     
GROUP BY 年度,季度     
ORDER BY 年度,季度

方法二用到了CASE WHEN THEN ELSE多条件判断。当月份为(1,2,3)时，季度为1；月份为(4,5,6)时，季度为2；月份为(7,8,9)时，季度为3；月份为(10,11,12)时，季度为4。

方法二在该题相对方法一较麻烦。

---

17. 查出订单总额超出5000的所有订单，客户名称，客户所在地区

SELECT 订单.订单ID, 订单.客户ID, 地区, SUM(ROUND((订单明细.`单价` * `数量` * (1 - 折扣)),2)) AS 总额
FROM 订单
LEFT JOIN 订单明细 ON 订单.`订单ID` = 订单明细.`订单ID`
LEFT JOIN 客户 ON 订单.`客户ID` = 客户.`客户ID`
GROUP BY 订单ID
HAVING 总额 > 5000
ORDER BY 总额 DESC

---

18. 查询哪些产品的年度销售额低于2000

SELECT YEAR(订单.订购日期) AS 年度,产品.产品名称, SUM(ROUND((订单明细.单价 * 数量 * (1 - 折扣)),2)) AS 销售额
FROM 订单明细
LEFT JOIN 订单 ON 订单.`订单ID` = 订单明细.`订单ID`
LEFT JOIN 产品 ON 订单明细.`产品ID` = 产品.`产品ID`
GROUP BY 年度, 产品名称
HAVING 销售额 < 2000
ORDER BY 年度

---

19. 查询所有订单ID开头为102的订单

SELECT *
FROM 订单
WHERE 订单ID LIKE '102%'

---

20. 查询所有“中硕贸易”，“学仁贸易”，“正人资源”，“中通”客户的订单，（要求使用in函数）

SELECT *
FROM 订单, 客户
WHERE 订单.`客户ID` = 客户.`客户ID`
AND 客户.`公司名称` IN ('中硕贸易', '学仁贸易', '正人资源', '中通')

🔗🔗🔗

数据和题目见：SQL查询语句练习