我最后选择了算法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. 本文为了逻辑清晰，写出的查询性能并不是很好，有很多可以提升的地方。大家在把三段查询合并的时候可以从全局进行考虑来调校查询性能。
后记
非常感谢“es潇潇”的反馈。原文第二部分第二段的SQL查询中，原先使用的是内连接，这会使新购置的车辆无法被预订到。这是本文代码的疏漏，对原文的SQL语句已进行修正。
“es潇潇”的问题又给了我两个方面的思考：
1. 我们的门店可以根据地域分成组，当在一个门店无法预订到指定车型时，可以在同一组的其他门店查找合适车辆进行预订。对于在一个区域中的门店可以通过内部调车来满足顾客的需求。这样的改动对于顾客来说又是完全透明的。要做这样的改动就需要增加新表，并修改原先的查询逻辑，大家可以进一步思考。
2. “当我们有一把锤子时，看到的任何东西都像钉子”。对于我来说我的锤子就是SQL了，所以我把选车的逻辑放在了SQL查询中。这样是否好呢？是否把候选车辆集返回给前台应用服务器来做逻辑筛选更好呢？这里要权衡两个资源要素。SQL端进行筛选，损失了数据库的CPU等资源，但数据库到应用服务器间的网络负载减轻了，在Application进行筛选就正好反过来。如何进行选择呢？
大家的反馈印证了，集众智而有所进这句话。再次感谢大家的支持与建议。
相关链接：
