根据指定时间找出最接近的预约时间
数据库环境:SQL SERVER 2008R2
有用户表和预约时间表如下:
用户表
预约时间表
要求得到如下结果
有以下限制条件:
1.有预约时间大于等于今天的,取最早的日期
2.没有大于今天的预约时间,取过去预约时间最近的一条
3.没有预约时间的,则预约时间显示为NULL
初看题目时,觉得很简单,用分析函数整一下就OK了。但真正去实现的时候,发现并不是自己想象的那么简单。
用分析函数整了一会,没有什么好的思路,就暂时把预约时间表分成2部分,一部分是预约时间大于等于今天的,
另一部分则是预约时间小于今天。分别排序后用union all关联起来。
1.建表,准备测试数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE users (id INT,username VARCHAR(10)) INSERT INTO users(id,username) VALUES(1,'张三') INSERT INTO users(id,username) VALUES(2,'李四') INSERT INTO users(id,username) VALUES(3,'王五') CREATE TABLE appointment(id INT,userid INT,ordertime DATE) INSERT INTO appointment(id,userid,ordertime)VALUES(1,1,'2015-07-30') INSERT INTO appointment(id,userid,ordertime)VALUES(2,1,'2015-07-23') INSERT INTO appointment(id,userid,ordertime)VALUES(3,2,'2015-07-26') INSERT INTO appointment(id,userid,ordertime)VALUES(4,1,'2015-07-31') INSERT INTO appointment(id,userid,ordertime)VALUES(5,2,'2015-07-21')
2.union all实现
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
WITH x0 AS ( /*大于等于今天的预约时间分组取最小值*/ SELECT userid , MIN(ordertime) ordertime FROM dbo.appointment WHERE ordertime >= GETDATE() GROUP BY userid UNION ALL /*小于今天的预约时间分组取最大值*/ SELECT userid , MAX(ordertime) ordertime FROM dbo.appointment WHERE ordertime < GETDATE() GROUP BY userid ),/*合并之后可能会存在一个客户有2个预约时间段,将结果集分组取最大值*/ x1 AS ( SELECT userid , MAX(ordertime) ordertime FROM x0 GROUP BY userid ) SELECT u.username , p.ordertime FROM users u LEFT JOIN x1 p ON p.userid = u.id
再回来审了一下题,发觉自己钻进死胡同里头了。其实,通过case when比较预约时间和今天的大小,
然后用group by分组求就行。
3.case when+group by实现
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
WITH x0 AS ( SELECT userid , MAX(CASE WHEN ordertime < GETDATE() THEN ordertime END) AS max_ordertime ,--早于今天,取预约时间最大值 MIN(CASE WHEN ordertime >= GETDATE() THEN ordertime END) AS min_ordertime --预约时间大于等于今天,取预约时间最小值 FROM appointment GROUP BY userid ) SELECT a.username , ISNULL(b.min_ordertime, b.max_ordertime) AS ordertime FROM users a LEFT JOIN x0 b ON b.userid = a.id
绕来绕去,就这么简单!