【SQL边干边学系列】05高级问题

文章目录

前言
回顾
高级问题
答案
未完待续

前言

在这里插入图片描述
该系列教程，将会从实际问题出发，边干边学，逐步深入讲解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