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

数据库环境：SQL SERVER 2008R2

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

用户表

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实现

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