租车信息系统数据库设计（5）
前篇回顾
从租车信息系统数据库设计（1）至租车信息系统数据库设计（4）我们完成了一个简单的租车信息系统的数据库设计。
从功能上来讲还有很多可以扩展的方面，如权限管理、发票管理等等，本文不将展开。大家可以对这些需求进行设想，设计相应的表、字段和关联，并融合到整体设计中。
本篇是本系列的最后一篇，我们将利用先前设计的数据库结构来写一些查询，完成一些业务需求，同时也反过来审视先前的设计。
获取需要催促还车的订单
我们的业务人员每天都要获取超出预订期限未还车的订单。对于这些订单，业务人员需要一一电话客户。
那就让我们来帮助业务人员写这个查询吧！
select RentalOrder.Order_ID from Table_Order RentalOrder where RentalOrder.Order_BookEndDate < GETDATE() and OrderStatus_ID = (select OrderStatus.OrderStatus_ID from Table_OrderStatus OrderStatus where OrderStatus.OrderStatus_Name = 'Inuse')这个查询把订单预订终止日期比当前时间早，但订单状态还是‘Inuse’的订单好找了出来（订单状态种类请参见租车信息系统数据库设计（2））。
注：1. 此查询中有一个子查询，大家可以直接使用查出来的OrderStatus_ID代替之，这样对性能有好处。此处是为了增加可读性。
2. 此查询仅输出了Order_ID，大家可根据需要连接相应的表，输出需要的字段。
顾客预订用车
当某顾客决定要租车时，他会在租车网站上选定某型号的车、哪个门店、某个时间段，并点击预订。
此时系统要查找指定门店的该车型车辆在相应时间是否有档期，我们分步骤来写这个查询。
查询的输入为：
1. 车型ID：@CarCategory_ID
2. 门店ID：@Store_ID
3. 租用起始日期：@StartDate
4. 租用结束日期：@EndDate
查询的输出为：
符合条件的车辆ID：Car_ID
--1 选出指定车型（Category），指定门店（Store），且车辆状态为Ready和Inuse的车辆
select
Car_ID
into
#CandidateCars1
from
Table_Car
where
CarCategory_ID = @CarCategory_ID
and
Store_ID = @Store_ID
and
CarStatus_ID in (select
CarStatus_ID
from
Table_CarStatus
where
CarStatus_Name in ('Ready', 'Inuse'));
注：1. 我们把第一步的查询结果放到临时表中，供下一步使用。
2. 车辆状态的枚举值请参见租车信息系统数据库设计（1）。车辆状态为Inuse表示此时该车正在被租用，不在车库中（但对于未来某段时间的预订，该车可能会有档期）。车辆状态和订单状态都有一个Inuse的枚举值，请不要混淆。
3. 此查询中的子查询可以用查出的ID值代替。
--2 在候选车辆中，选出在@StartDate和@EndDate之间有档期的车辆 select distinct CandidateCars.Car_ID into #CandidateCars2 from #CandidateCars1 CandidateCars inner join Table_Order RentalOrder on CandidateCars.Car_ID = RentalOrder.Car_ID where RentalOrder.Order_BookStartDate > @EndDate or RentalOrder.Order_BookEndDate < @StartDate;
到第二步结束我们的结果集中可能会有多辆符合条件的车。那到底选哪一辆车最合适呢？
我首先想到的是尽可能把车辆的档期排满。那就是一辆一辆的来排，第一辆排不下了，再排到第二辆，这样也许能使车辆的利用率最高。
但这种方案很快就被我排除了，原因有3个方面：
1. 这种方式会使某些车辆的租用很稠密，而另一些车可能一个订单都没有。
2. 某些车太稠密的订单容易产生意外情况，如前一个租车的顾客还车晚了，造成后面的顾客无法领到订单中的车辆。这种情况虽然可以通过临时更改车辆，但需要人为处理的意外境况增多了，且还需要修改原先的订单中的车辆信息。
3. 某些车太稠密的订单又会使某些车辆的使用率特别高，造成其维修率和折损率也特别高。
所以在第三步选车策略的目标是使每辆车的租用尽可能平均，车辆的租用间隔稀疏一些。相应的选车策略可以有：
1. 选从当前时间开始，被预订的时间总和最少的那辆车
2. 选从当前时间开始，被预订次数最少的那辆车
3. 选之前预订时间段与本次预订时间段前后间隔最大的那辆车
4. 随机选择一辆车
这几种策略哪个最好，我很难说，可能需要进行一些实验。大家可以思考讨论。
在这里我选择第3个策略来写查询。第3个策略相对于其他的策略从文字上稍难理解一些，我先来看一个图示：
图中是某辆车的预订时间轴，我希望本次预订期与之前的预订期之间的间隔尽可能大，这样当某顾客还车迟了时就能有多一些的缓冲时间。
要用SQL实现这一策略，我想到了2种算法：
1. 把前后间隔时间求和后倒序排列，取Top 1的记录
2. 先按与‘之前预订期1’的间隔时间倒序排列后取Top 2的2条记录，再拿这2条记录按与‘之前预订期2’的间隔时间倒序排列后取Top 1的记录
我最后选择了算法1来写SQL语句
--3 在候选车辆中，选之前预订时间段与本次预订时间段前后间隔最大的那辆车 With CandidateCarsWithDate as ( select CandidateCars.Car_ID, (select MAX(RentalOrder1.Order_BookEndDate) from Table_Order RentalOrder1 where RentalOrder1.Car_ID = CandidateCars.Car_ID and RentalOrder1.Order_BookEndDate < @StartDate) MaxBeforeDate, (select MIN(RentalOrder2.Order_BookStartDate) from Table_Order RentalOrder2 where RentalOrder2.Car_ID = CandidateCars.Car_ID and RentalOrder2.Order_BookStartDate > @EndDate) MinAfterDate from #CandidateCars2 CandidateCars ) select top(1) Car_ID from CandidateCarsWithDate order by DATEDIFF(day, ISNULL(MaxBeforeDate, '1900-01-01'), ISNULL(MinAfterDate, '9999-01-01')) desc;
我使用了CTE表达式来写这个查询，语句在SQL Server 2008中测试通过。
注：1. With中的查询包含了两个子查询，分别获得比本次预订起始日期早的最大的终止日期和比本次预订终止日期晚的最小的起始日期。
2. 在With子查询下的select语句，按照With中计算得到的MinAfterDate和MaxBeforeDate的差值降序排列（按照算法描述，应该减去本次预订的间隔天数，但这不会影响排序结果），并取出Top 1的车辆ID。
3. 最后一行使用ISNULL的原因是在本次预订之前或之后可能没有其他预订，故取了两个特别的日期。
至此我们得到了符合条件的车辆ID了。
总结与思考
本篇我们设想了2个需求，并撰写了查询。‘获取需要催促还车的订单’较为简单一些，我就不多说什么了。对于‘顾客预订用车’相对复杂些，我再多说两句。
1. 对于‘顾客预订用车’，我为了把逻辑清晰分成了3小段，使用了2个临时表。大家可以考虑把这些逻辑合在一起，写成一个CTE或使用嵌套子查询（如果大家使用临时表，记得在最后把这些表drop掉）。
2. 大家可以再进一步加入用户验证和生成Order的逻辑后封装成存储过程供应用程序端调用。
3. 本文中选用的策略和算法未必是最优的，大家可以进一步思考、探索与分享。
4. 本文为了逻辑清晰，写出的查询性能并不是很好，有很多可以提升的地方。大家在把三段查询合并的时候可以从全局进行考虑来调校查询性能。
"