文章目录
- 前言
- 回顾
- 高级问题
- 32.高价值客户
- 33.高价值的客户-总订单数
- 34.高价值的客户-带有折扣
- 35.月末订单
- 36.具有许多详细信息行的订单
- 答案
- 32.高价值客户
- 33.高价值的客户-总订单数
- 34.高价值的客户-带有折扣
- 35.月末订单
- 36.具有许多详细信息行的订单
- 未完待续
前言
该系列教程，将会从实际问题出发，边干边学，逐步深入讲解SQL的各方面知识。
你需要完成所有的问题吗？绝对不是。介绍性的问题相当简单，所以你可以直接跳过到“中级问题”部分。如果你不是初学者，但不确定应该从哪里开始，请在“入门问题”部分看看问题和预期结果，并确保你理解这些概念。如果已经理解了这些概念，请开始阅读“中级问题”部分。
你想从这本书中复制代码并在你的服务器上运行？我建议你手动输入，而不是复制粘贴。为什么要去麻烦地重新打字呢？科学表明，打字的行为会在你的脑中留下更深刻的印象。当你只是复制和粘贴时，代码只是直接从你电脑里的一个窗口转到另一个窗口，而不会给你留下多少印象。但是当你把它打出来时，你必须集中精力，这非常有助于保留信息。
一旦你完成了所有的问题，将拥有一些在数据分析和高级Select语句使用方面非常有用的技能。当然，这并不是SQL的全部内容。还有修改数据（更新、插入、删除）、DDL（数据定义语言，即如何创建和修改数据库对象）、编程（如存储过程）和许多其他主题。
该系列教程中，只涉及到了使用Select语句检索数据的问题，这几乎是所有其他数据库主题的基础开端。
回顾
上篇文章👉《【SQL边干边学系列】04中级问题(续)》 讨论了剩余的中级问题。这篇开始讨论高级问题。
高级问题
32.高价值客户
我们想给所有的客户送一份特别的贵宾礼物。我们将高价值的客户定义为那些至少有1个订单，总价（不包括折扣）等于1万元或以上的客户。我们只想考虑2016年的订单。
-- 预期结果
CustomerID CompanyName OrderID TotalOrderAmount
---------- ---------------------------------------- ----------- ---------------------
QUICK QUICK-Stop 10865 17250.00
SAVEA Save-a-lot Markets 11030 16321.90
HANAR Hanari Carnes 10981 15810.00
KOENE Königlich Essen 10817 11490.70
RATTC Rattlesnake Canyon Grocery 10889 11380.00
HUNGO Hungry Owl All-Night Grocers 10897 10835.24
(6 row(s) affected)
提示如下
首先，让我们得到2016年所有订单的必要字段。不要使用分组，只使用Where子句。你将需要CustomerID、Customers表的CompanyName、Orders表中的OrderID、以及OrderDetails表中的Quantity和unit price。订单按总订单金额降序排序。
你应该有类似的语句：
Select
Customers.CustomerID
,Customers.CompanyName
,Orders.OrderID
,Amount = Quantity * UnitPrice
From Customers
join Orders
on Orders.CustomerID = Customers.CustomerID
join OrderDetails
on Orders.OrderID = OrderDetails.OrderID
Where
OrderDate >= '20160101'
and OrderDate < '20170101'
这给出了2016年的每个订单的总金额。现在，你需要对哪些字段进行分组，哪些字段进行求和？
Select
Customers.CustomerID
,Customers.CompanyName
,Orders.OrderID
,TotalOrderAmount = sum(Quantity * UnitPrice)
From Customers
Join Orders
on Orders.CustomerID = Customers.CustomerID
Join OrderDetails
on Orders.OrderID = OrderDetails.OrderID
Where
OrderDate >= '20160101'
and OrderDate < '20170101'
Group By
Customers.CustomerID
,Customers.CompanyName
,Orders.OrderID
客户和订单级别的字段需要按分组，并对总金额进行汇总。
为了获得1万元或更多的订单？能直接写在Where子句中吗？
33.高价值的客户-总订单数
经理已经改变了主意。他不要求客户至少拥有一个总计1万元或以上的单独订单，而是将高价值客户定义为2016年拥有总计1.5万元或以上订单的客户。你将如何改变上述问题的答案？
-- 预期结果
CustomerID CompanyName TotalOrderAmount
---------- ---------------------------------------- ---------------------
SAVEA Save-a-lot Markets 42806.25
ERNSH Ernst Handel 42598.90
QUICK QUICK-Stop 40526.99
HANAR Hanari Carnes 24238.05
HUNGO Hungry Owl All-Night Grocers 22796.34
RATTC Rattlesnake Canyon Grocery 21725.60
KOENE Königlich Essen 20204.95
FOLKO Folk och fä HB 15973.85
WHITC White Clover Markets 15278.90
(9 row(s) affected)
提示：这个查询几乎与上面的查询相同，但只需要删除几行查询或注释掉，以便在不同的级别上进行分组。
34.高价值的客户-带有折扣
更改上述查询，以便在计算高价值客户时使用折扣。按包括折扣在内的总金额。
-- 预期结果
CustomerID CompanyName TotalsWithoutDiscount TotalsWithDiscount
---------- ------------------------------ --------------------- ----------------------
ERNSH Ernst Handel 42598.90 41210.6500244141
QUICK QUICK-Stop 40526.99 37217.3150024414
SAVEA Save-a-lot Markets 42806.25 36310.1097793579
HANAR Hanari Carnes 24238.05 23821.1999893188
RATTC Rattlesnake Canyon Grocery 21725.60 21238.2704410553
HUNGO Hungry Owl All-Night Grocers 22796.34 20402.119934082
KOENE Königlich Essen 20204.95 19582.7739868164
WHITC White Clover Markets 15278.90 15278.8999862671
FOLKO Folk och fä HB 15973.85 13644.0674972534
SUPRD Suprêmes délices 11862.50 11644.5999984741
BOTTM Bottom-Dollar Markets 12227.40 11338.5500488281
(11 row(s) affected)
提示如下
首先，只需使用OrderDetails表。您需要弄清楚折扣字段的结构方式。
你应该进行这样的操作;
Select
OrderID
,ProductID
,UnitPrice
,Quantity
,Discount
,TotalWithDisccount = UnitPrice * Quantity * (1- Discount)
from OrderDetails
请注意，折扣是以百分比的形式呈现的。因此，如果在折扣字段中有一个0.15，那么你需要将UnitPrice乘以Quantity再乘以0.85（1.00-0.15）。你需要使用(1 -折扣）以确保首先完成折扣计算。
35.月末订单
在月底，销售人员可能会更努力地获得订单，以满足他们的月底配额。显示在每月的最后一天发出的所有订单。按EmployeeID和OrderID排序。
-- 预期结果
EmployeeID OrderID OrderDate
----------- ----------- -----------------------
1 10461 2015-02-28 00:00:00.000
1 10616 2015-07-31 00:00:00.000
2 10583 2015-06-30 00:00:00.000
2 10686 2015-09-30 00:00:00.000
2 10989 2016-03-31 00:00:00.000
2 11060 2016-04-30 00:00:00.000
3 10432 2015-01-31 00:00:00.000
3 10806 2015-12-31 00:00:00.000
3 10988 2016-03-31 00:00:00.000
3 11063 2016-04-30 00:00:00.000
4 10343 2014-10-31 00:00:00.000
4 10522 2015-04-30 00:00:00.000
4 10584 2015-06-30 00:00:00.000
4 10617 2015-07-31 00:00:00.000
4 10725 2015-10-31 00:00:00.000
4 10807 2015-12-31 00:00:00.000
4 11061 2016-04-30 00:00:00.000
4 11062 2016-04-30 00:00:00.000
5 10269 2014-07-31 00:00:00.000
6 10317 2014-09-30 00:00:00.000
7 10490 2015-03-31 00:00:00.000
8 10399 2014-12-31 00:00:00.000
8 10460 2015-02-28 00:00:00.000
8 10491 2015-03-31 00:00:00.000
8 10987 2016-03-31 00:00:00.000
9 10687 2015-09-30 00:00:00.000
(26 row(s) affected)
提示：你可以自己计算这个日期，比如DateAdd和DateDiff函数。
36.具有许多详细信息行的订单
显示详细信息行最多的10个订单。
-- 预期结果
OrderID TotalOrderDetails
----------- -----------------
11077 25
10979 6
10657 6
10847 6
10845 5
10836 5
10714 5
10670 5
10691 5
10698 5
(10 row(s) affected)
提示：使用Orders和 OrderDetails，使用 Group by和 count()功能。
答案
32.高价值客户
答案
Select
Customers.CustomerID
,Customers.CompanyName
,Orders.OrderID
,TotalOrderAmount = SUM(Quantity * UnitPrice)
From Customers
Join Orders
on Orders.CustomerID = Customers.CustomerID
Join OrderDetails
on Orders.OrderID = OrderDetails.OrderID
Where
OrderDate >= '20160101'
and OrderDate < '20170101'
Group by
Customers.CustomerID
,Customers.CompanyName
,Orders.Orderid
Having Sum(Quantity * UnitPrice) > 10000
Order by TotalOrderAmount DESC
讨论
如果你尝试把这个过滤器and sum(Quantity * UnitPrice) >= 10000放在Where字句中，将得到错误。聚合函数过滤只能用在 Having子句中（除了某些例外），而不是Where子句。
33.高价值的客户-总订单数
答案
Select
Customers.CustomerID
,Customers.CompanyName
--,Orders.OrderID
,TotalOrderAmount = SUM(Quantity * UnitPrice)
From Customers
Join Orders
on Orders.CustomerID = Customers.CustomerID
Join OrderDetails
on Orders.OrderID = OrderDetails.OrderID
Where
OrderDate >= '20160101'
and OrderDate < '20170101'
Group by
Customers.CustomerID
,Customers.CompanyName
--,Orders.Orderid
Having sum(Quantity * UnitPrice) > 15000
Order by TotalOrderAmount desc;
讨论
这里所需要的只是注释掉Group By子句中对Orders.Orderid的引用。通过这样做，我们是在客户级别进行分组，而不是在订单级别。
34.高价值的客户-带有折扣
答案
Select
Customers.CustomerID
,Customers.CompanyName
,TotalsWithoutDiscount = SUM(Quantity * UnitPrice)
,TotalsWithDiscount = SUM(Quantity * UnitPrice * (1- Discount))
From Customers
Join Orders
on Orders.CustomerID = Customers.CustomerID
Join OrderDetails
on Orders.OrderID = OrderDetails.OrderID
Where
OrderDate >= '20160101'
and OrderDate < '20170101'
Group by
Customers.CustomerID
,Customers.CompanyName
Having sum(Quantity * UnitPrice * (1- Discount)) > 10000
Order by TotalsWithDiscount DESC;
讨论
请注意，你需要对“Select”子句、“Having”子句以及“ Order by”子句中的带有折扣的订单总额使用新的计算方法。在 Order by子句中，您可以重复使用在Select子句中创建的别名，但在Having子句中，您需要重复计算。
35.月末订单
答案
Select
EmployeeID
,OrderID
,OrderDate
From Orders
Where OrderDate = EOMONTH(OrderDate )
Order by
EmployeeID
,OrderID
讨论
EOMONTH函数是在SQL Server 2012中引入的，所以在此之前，开发人员必须使用如下函数组合：
Where OrderDate = dateadd(month,1 + datediff(month,0,OrderDate),-1)
36.具有许多详细信息行的订单
答案
Select top 10
Orders.OrderID
,TotalOrderDetails = count(*)
From Orders
Join OrderDetails
on Orders.OrderID = OrderDetails.OrderID
Group By Orders.OrderID
Order By count(*) desc
讨论
当你选择前50名而不是前10名时，会发生什么呢？会有更多总数为5的行出现。如果你想显示所有的结果，你可以使用以下操作：
Select top 10 With Ties
Orders.OrderID
,TotalOrderDetails = count(*)
From Orders
Join OrderDetails
on Orders.OrderID = OrderDetails.OrderID
Group By Orders.OrderID
Order By count(*) desc
注意，相同的查询，使用“With Ties”关键字，现在返回37行，因为有许多行的值为5。
未完待续
今天我们讨论了高级问题中的前5个问题，下一次我们接着讲其它高级问题。
如果喜欢这篇文章，请不要忘记关注🧡、点赞👍和收藏📔哦！
