根据指定时间找出最接近的预约时间

数据库环境：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

绕来绕去，就这么简单！