數據庫環境：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