文章目录
- 前言
- 回顾
- 高级问题
- 41.逾期订单
- 42.逾期订单-哪些员工?
- 43.逾期订单与总订单相比
- 44.逾期订单与总订单相比 - 丢失的员工
- 45.逾期订单与总订单相比 - 修复null
- 46.逾期订单与总订单之间的百分比
- 47.逾期订单与总订单相比 - 修正decimal
- 答案
- 41.逾期订单
- 42.逾期订单-哪些员工?
- 43.逾期订单与总订单相比
- 44.逾期订单与总订单相比 - 丢失的员工
- 45.逾期订单与总订单相比 - 修复null
- 46.逾期订单与总订单之间的百分比
- 47.逾期订单与总订单相比 - 修正decimal
- 未完待续
前言
该系列教程,将会从实际问题出发,边干边学,逐步深入讲解SQL的各方面知识。
你需要完成所有的问题吗?绝对不是。介绍性的问题相当简单,所以你可以直接跳过到“中级问题”部分。如果你不是初学者,但不确定应该从哪里开始,请在“入门问题”部分看看问题和预期结果,并确保你理解这些概念。如果已经理解了这些概念,请开始阅读“中级问题”部分。
你想从这本书中复制代码并在你的服务器上运行?我建议你手动输入,而不是复制粘贴。为什么要去麻烦地重新打字呢?科学表明,打字的行为会在你的脑中留下更深刻的印象。当你只是复制和粘贴时,代码只是直接从你电脑里的一个窗口转到另一个窗口,而不会给你留下多少印象。但是当你把它打出来时,你必须集中精力,这非常有助于保留信息。
一旦你完成了所有的问题,将拥有一些在数据分析和高级Select语句使用方面非常有用的技能。当然,这并不是SQL的全部内容。还有修改数据(更新、插入、删除)、DDL(数据定义语言,即如何创建和修改数据库对象)、编程(如存储过程)和许多其他主题。
该系列教程中,只涉及到了使用Select语句检索数据的问题,这几乎是所有其他数据库主题的基础开端。
回顾
上篇文章👉《【SQL边干边学系列】06高级问题-2》 开始讨论高级问题,这篇我们接着讨论。
高级问题
41.逾期订单
一些客户抱怨他们的订单迟到了。哪些订单迟到了?
-- 预期结果
OrderID OrderDate RequiredDate ShippedDate
----------- ---------- ------------ -----------
10264 2014-07-24 2014-08-21 2014-08-23
10271 2014-08-01 2014-08-29 2014-08-30
10280 2014-08-14 2014-09-11 2014-09-12
10302 2014-09-10 2014-10-08 2014-10-09
10309 2014-09-19 2014-10-17 2014-10-23
10380 2014-12-12 2015-01-09 2015-01-16
10423 2015-01-23 2015-02-06 2015-02-24
10427 2015-01-27 2015-02-24 2015-03-03
10433 2015-02-03 2015-03-03 2015-03-04
10451 2015-02-19 2015-03-05 2015-03-12
10483 2015-03-24 2015-04-21 2015-04-25
10515 2015-04-23 2015-05-07 2015-05-23
......
10970 2016-03-24 2016-04-07 2016-04-24
10978 2016-03-26 2016-04-23 2016-04-23
10998 2016-04-03 2016-04-17 2016-04-17
(39 row(s) affected)
提示:要确定哪些订单会延迟,可以组合使用RequiredDate和ShippedDate。如果ShippedDate实际上已经晚于RequiredDate,你可以确定它已经晚了。
42.逾期订单-哪些员工?
有些销售人员逾期的订单比其他人多。也许他们没有跟进订购流程,需要更多的培训。哪些销售人员的订单逾期最多?
-- 预期结果
EmployeeID LastName TotalLateOrders
----------- -------------------- ---------------
4 Peacock 10
3 Leverling 5
8 Callahan 5
9 Dodsworth 5
7 King 4
2 Fuller 4
1 Davolio 3
6 Suyama 3
(8 row(s) affected)
提示:上面这个问题的答案是一个很好的起点。你需和“ Employee”表Join以获得last name,并添加“Count”来显示逾期订单总数。
43.逾期订单与总订单相比
仅仅查看每个销售人员逾期的订单数量并不是一个好主意。它需要与每个销售人员的订单总数进行比较。返回如下结果:
-- 预期结果
EmployeeID LastName AllOrders LateOrders
----------- -------------------- ----------- -----------
1 Davolio 123 3
2 Fuller 96 4
3 Leverling 127 5
4 Peacock 156 10
6 Suyama 67 3
7 King 72 4
8 Callahan 104 5
9 Dodsworth 43 5
(8 row(s) affected)
提示如下
你可以在一个查询中使用多个CTE。这将是解决这个问题的一种直接的方法。
下面是2个SQL语句,它们可以放入CTE中,并一起放入最终的SQL语句中。
-- Late orders
Select
EmployeeID
,TotalOrders = Count(*)
From Orders
Where
RequiredDate <= ShippedDate
Group By
EmployeeID
-- Total orders
Select
EmployeeID
,TotalOrders = Count(*)
From Orders
Group By
EmployeeID
44.逾期订单与总订单相比 - 丢失的员工
在上面这个问题的答案中缺少了一个员工。修复SQL,以显示所有已接受订单的员工。
-- 预期结果
EmployeeID LastName AllOrders LateOrders
----------- -------------------- ----------- -----------
1 Davolio 123 3
2 Fuller 96 4
3 Leverling 127 5
4 Peacock 156 10
5 Buchanan 42 NULL
6 Suyama 67 3
7 King 72 4
8 Callahan 104 5
9 Dodsworth 43 5
(9 row(s) affected)
提示:当你只运行 AllOrders CTE时,返回了多少行?如果你只运行LateOrders CTE,返回了多少行?应该添加一个左连接(也称为左外部连接),以确保我们显示每一行,即使没有逾期订单。
45.逾期订单与总订单相比 - 修复null
继续上面查询的答案,让我们修复第5行的结果。在逾期订单中应该有0而不是null。
-- 预期结果
EmployeeID LastName AllOrders LateOrders
----------- -------------------- ----------- -----------
1 Davolio 123 3
2 Fuller 96 4
3 Leverling 127 5
4 Peacock 156 10
5 Buchanan 42 0
6 Suyama 67 3
7 King 72 4
8 Callahan 104 5
9 Dodsworth 43 5
(9 row(s) affected)
提示:找到一个函数来测试一个值是否为空,并在该值为null时返回一个不同的值。
46.逾期订单与总订单之间的百分比
现在我们想知道逾期订单占总订单的比例。
-- 预期结果
EmployeeID LastName AllOrders LateOrders PercentLateOrders
----------- -------------------- ----------- ----------- -------------------
1 Davolio 123 3 0.0243902439024
2 Fuller 96 4 0.0416666666666
3 Leverling 127 5 0.0393700787401
4 Peacock 156 10 0.0641025641025
5 Buchanan 42 0 0.0000000000000
6 Suyama 67 3 0.0447761194029
7 King 72 4 0.0555555555555
8 Callahan 104 5 0.0480769230769
9 Dodsworth 43 5 0.1162790697674
(9 row(s) affected)
提示如下
通过将逾期订单除以总订单,应该能够得到百分比。然而,人们会遇到一个常见的问题,即一个整数除以一个整数返回一个整数。例如,如果运行这条SQL:select 3/2
,你将得到1而不是1.5,因为它将返回最接近的整数。
47.逾期订单与总订单相比 - 修正decimal
为了使输出更容易读取,我们让百分比字段保留2位小数。
-- 预期结果
EmployeeID LastName AllOrders LateOrders PercentLateOrders
----------- -------------------- ----------- ----------- -------------------
1 Davolio 123 3 0.02
2 Fuller 96 4 0.04
3 Leverling 127 5 0.04
4 Peacock 156 10 0.06
5 Buchanan 42 0 0.00
6 Suyama 67 3 0.04
7 King 72 4 0.06
8 Callahan 104 5 0.05
9 Dodsworth 43 5 0.12
(9 row(s) affected)
提示如下
一种简单的方法是显式地将PercentageLateOrders转换为特定的Decimal类型。使用Decimal类型,您可以指定小数点右侧的位数。
计算PercentLateOrders变得有点长和复杂,要使所有的逗号和括号都正确可能很棘手。
简化它的一种方法是用一个实际的值进行验证。例如:
Select convert(decimal(10,2), 0.0243902439024)
答案
41.逾期订单
答案
Select
OrderID
,OrderDate = convert(date, OrderDate)
,RequiredDate = convert(date, RequiredDate)
,ShippedDate = convert(date, ShippedDate)
From Orders
Where
RequiredDate <= ShippedDate
42.逾期订单-哪些员工?
答案
Select
Employees.EmployeeID
,LastName
,TotalLateOrders = Count(*)
From Orders
Join Employees
on Employees.EmployeeID = Orders.EmployeeID
Where
RequiredDate <= ShippedDate
Group By
Employees.EmployeeID
,Employees.LastName
Order by TotalLateOrders desc
讨论
请注意,“Employees”表中的LastName和EmployeeID都需要包含在Group by子句中,否则我们将得到错误信息。
从技术上讲,EmployeeID是一个主键字段,由于我们已经按其进行分组,只有一个LastName与EmployeeID相关联。但是,数据库引擎并不知道这一点,并且仍然需要在 Group by子句中包含LastName。
43.逾期订单与总订单相比
答案
;With LateOrders as (
Select
EmployeeID
,TotalOrders = Count(*)
From Orders
Where
RequiredDate <= ShippedDate
Group By
EmployeeID
)
, AllOrders as (
Select
EmployeeID
,TotalOrders = Count(*)
From Orders
Group By
EmployeeID
)
Select
Employees.EmployeeID
,LastName
,AllOrders = AllOrders.TotalOrders
,LateOrders = LateOrders.TotalOrders
From Employees
Join AllOrders
on AllOrders.EmployeeID = Employees.EmployeeID
Join LateOrders
on LateOrders.EmployeeID = Employees.EmployeeID
讨论
上面的查询几乎是正确的,但是如果你仔细注意,你会意识到它有一个小问题。我们将在下一个问题中了解到更多信息。
44.逾期订单与总订单相比 - 丢失的员工
答案
;With LateOrders as (
Select
EmployeeID
,TotalOrders = Count(*)
From Orders
Where
RequiredDate <= ShippedDate
Group By
EmployeeID
)
, AllOrders as (
Select
EmployeeID
,TotalOrders = Count(*)
From Orders
Group By
EmployeeID
)
Select
Employees.EmployeeID
,LastName
,AllOrders = AllOrders.TotalOrders
,LateOrders = LateOrders.TotalOrders
From Employees
Join AllOrders
on AllOrders.EmployeeID = Employees.EmployeeID
Left Join LateOrders
on LateOrders.EmployeeID = Employees.EmployeeID
讨论
如果我们展示所有的员工(包括那些没有订单的员工),我们需要使用左连接。
45.逾期订单与总订单相比 - 修复null
答案
;With LateOrders as (
Select
EmployeeID
,TotalOrders = Count(*)
From Orders
Where
RequiredDate <= ShippedDate
Group By
EmployeeID
)
, AllOrders as (
Select
EmployeeID
,TotalOrders = Count(*)
From Orders
Group By
EmployeeID
)
Select
Employees.EmployeeID
,LastName
,AllOrders = AllOrders.TotalOrders
,LateOrders = IsNull(LateOrders.TotalOrders, 0)
From Employees
Join AllOrders
on AllOrders.EmployeeID = Employees.EmployeeID
Left Join LateOrders
on LateOrders.EmployeeID = Employees.EmployeeID
讨论
在LateOrder上直接使用IsNull是解决这个问题的最佳方法。
另一种写方法是使用Case语句:
LateOrders =
Case
When LateOrders.TotalOrders is null Then 0
Else LateOrders.TotalOrders
End
但是,当你除了测试null之外不需要任何其他逻辑时,IsNull是最好的方法。
46.逾期订单与总订单之间的百分比
答案
;With LateOrders as (
Select
EmployeeID
,TotalOrders = Count(*)
From Orders
Where
RequiredDate <= ShippedDate
Group By
EmployeeID
)
, AllOrders as (
Select
EmployeeID
,TotalOrders = Count(*)
From Orders
Group By
EmployeeID
)
Select
Employees.EmployeeID
,LastName
,AllOrders = AllOrders.TotalOrders
,LateOrders = IsNull(LateOrders.TotalOrders, 0)
,PercentLateOrders =
(IsNull(LateOrders.TotalOrders, 0) * 1.00) / AllOrders.TotalOrders
From Employees
Join AllOrders
on AllOrders.EmployeeID = Employees.EmployeeID
Left Join LateOrders
on LateOrders.EmployeeID = Employees.EmployeeID
讨论
如果你只是添加一个字段:
PercentLateOrders = LateOrders.TotalLateOrders/AllOrders.TotalOrders
所有字段都会得到0,尽管这显然是不正确的。但这就是当你把两个整数除在一起时所发生的情况。你需要将其中一个转换为decimal。转换为decimal数据类型的一种常见方法是乘以1.00。
请注意,在进行除法之前,你需要将整数转换decimal。如果你在除法之后做,就像这样:
(IsNull(LateOrders.TotalOrders, 0) / AllOrders.TotalOrders) * 1.00
你仍然会得到0。
47.逾期订单与总订单相比 - 修正decimal
答案
;With LateOrders as (
Select
EmployeeID
,TotalOrders = Count(*)
From Orders
Where
RequiredDate <= ShippedDate
Group By
EmployeeID
)
, AllOrders as (
Select
EmployeeID
,TotalOrders = Count(*)
From Orders
Group By
EmployeeID
)
Select
Employees.EmployeeID
,LastName
,AllOrders = AllOrders.TotalOrders
,LateOrders = IsNull(LateOrders.TotalOrders, 0)
,PercentLateOrders =
Convert(
Decimal (10,2)
,(IsNull(LateOrders.TotalOrders, 0) * 1.00) / AllOrders.TotalOrders
)
From Employees
Join AllOrders
on AllOrders.EmployeeID = Employees.EmployeeID
Left Join LateOrders
on LateOrders.EmployeeID = Employees.EmployeeID
讨论
四舍五入、截断和转换数据类型可能会变得复杂,而且有很多方法可能获得意想不到的结果。一定要仔细检查你的结果,并知道你是想要舍入,还是想要截断。
你可能已经注意到,我在计算中添加了一些新的行,以使它更容易阅读。这并不是必要的,但这是一种很好的编程实践,它更容易阅读和进行故障排除。
未完待续
下一篇我们接着讲剩余的高级问题。
如果喜欢这篇文章,请不要忘记关注🧡、点赞👍和收藏📔哦!