你的位置:首页 > 数据库

[数据库]根据指定时间找出最接近的预约时间


数据库环境:SQL SERVER 2008R2

有用户表和预约时间表如下:

用户表

预约时间表

要求得到如下结果

有以下限制条件:

1.有预约时间大于等于今天的,取最早的日期

2.没有大于今天的预约时间,取过去预约时间最近的一条

3.没有预约时间的,则预约时间显示为NULL

初看题目时,觉得很简单,用分析函数整一下就OK了。但真正去实现的时候,发现并不是自己想象的那么简单。

用分析函数整了一会,没有什么好的思路,就暂时把预约时间表分成2部分,一部分是预约时间大于等于今天的,

另一部分则是预约时间小于今天。分别排序后用union all关联起来。

1.建表,准备测试数据

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')

View Code

2.union all实现

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

View Code

再回来审了一下题,发觉自己钻进死胡同里头了。其实,通过case when比较预约时间和今天的大小,

然后用group by分组求就行。

3.case when+group by实现

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

View Code

绕来绕去,就这么简单!