SQL:现在有2张表，分别是用户表Users，预约表Appointment,如何获取最近一条预约日期？

[kw13202]

在开发过程中遇到跟标题一致的问题，现在是使用全部取出，然后在程序过滤的笨办法，这方法效率低，性能低，特来请教各位大神更好的解决方案。

现在有2张表，分别是用户表Users，预约表Appointment，结构和数据如下：
Users：

Appointment：

希望得到的结果是：
姓名 预约时间
张三 2015-7-30
李四 2014-7-26
王五 NULL

限制：
1.有大于等于今天的预约时间，显示离今天最近的一条
2.没有大于今天的预约时间，取过去预约时间最后一条
3.没有预约时间，则显示NULL

[Haytor]

另外一种方法，用子查询也可以实现，如下： SELECT a.ID AS UserID, a.UserName, ( CASE WHEN EXISTS( SELECT TOP 1 1 FROM Appointment b WHERE b.UserID = a.ID AND b.OrderTime >= DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0) ) THEN (SELECT MIN(b.OrderTime) FROM Appointment b WHERE b.UserID = a.ID AND b.OrderTime>= DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0) ) ELSE (SELECT MAX(b.OrderTime) FROM Appointment b WHERE b.UserID = a.ID AND b.OrderTime<= DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0) ) END ) AS OrderTime FROM Users a UserID UserName OrderTime 1 张三 2015-07-30 2 李四 2015-07-26 3 王五 NULL

[chinamobile851116]

CREATE TABLE #t1
(
id INT, 
NAME varchar(20)
)
INSERT INTO  #t1 VALUES(1,'a');
INSERT INTO  #t1 VALUES(2,'b');
INSERT INTO  #t1 VALUES(3,'c');

CREATE TABLE #t2
( id INT ,
userid INT ,
ordertime DATETIME
)
INSERT INTO  #t2 VALUES (1,1,'2015-07-30');
INSERT INTO  #t2 VALUES (2,1,'2015-07-23');
INSERT INTO  #t2 VALUES (3,2,'2015-07-26');
INSERT INTO  #t2 VALUES (4,1,'2015-07-31');
INSERT INTO  #t2 VALUES (5,2,'2015-07-21');
INSERT INTO  #t2 VALUES (6,1,'2015-07-26')
SELECT * FROM 
(SELECT * FROM #t1) a
LEFT JOIN 
(
SELECT userid,MAX(d) AS rq FROM 
(
SELECT userid,MAX(ordertime) AS d FROM #t2	WHERE ordertime<GETDATE() GROUP BY userid
UNION ALL
SELECT userid,MIN(ordertime) AS d FROM #t2	WHERE ordertime>=GETDATE() GROUP BY userid
) x 
GROUP BY x.userid
) b
ON a.id=b.userid


DROP TABLE #t1,#t2

我想到的是这个最笨的办法

[xxfvba]

select username,ordertime from users a left join (select *,rn=row_number() over (partition by UserId order by ordertime) from Appointment where ordertime <=getdate()) b on a.Id=b.UserId and rn=1 where a.id is not null

[Tiger_Zhao]

/* 测试数据
WITH Users(ID,UserName) AS (
    SELECT 1,'张三' UNION ALL
    SELECT 2,'李四' UNION ALL
    SELECT 3,'王五'
)
,Appointment(ID,UserID,OrderTime) AS (
    SELECT 1,1,'2015-07-30' UNION ALL
    SELECT 2,1,'2015-07-23' UNION ALL
    SELECT 3,2,'2015-07-26' UNION ALL
    SELECT 4,1,'2015-07-31' UNION ALL
    SELECT 5,2,'2015-07-21' UNION ALL
    SELECT 6,1,'2015-07-26'
) */
    SELECT u.UserName,
           ISNULL(a1.OrderTime, a2.OrderTime) OrderTime
      FROM Users u
 LEFT JOIN (
                SELECT UserID,
                       MIN(OrderTime) OrderTime
                  FROM Appointment
                 WHERE OrderTime >= GETDATE()
              GROUP BY UserID
           ) a1
        ON u.ID = a1.UserID
 LEFT JOIN (
                SELECT UserID,
                       MAX(OrderTime) OrderTime
                  FROM Appointment
                 WHERE OrderTime < GETDATE()
              GROUP BY UserID
           ) a2
        ON u.ID = a2.UserID

UserName OrderTime
-------- ----------
张三     2015-07-30
李四     2015-07-26
王五     NULL

只要在 Appointment(UserID,OrderTime) 上有索引，就不会慢

[许晨旭]

;with cte as
(
	select ROW_NUMBER() over(PARTITION by userid order by ordertime desc) as row,
	       userid,
	       ordertime
	from #t2
)

select b.NAME,a.ordertime
from cte a
join #t1 b on a.userid=b.id
where a.row=1

NAME                 ordertime
-------------------- -----------------------
a                    2015-07-31 00:00:00.000
b                    2015-07-26 00:00:00.000

(2 行受影响)

[许晨旭]

刚才写错了

;with cte as
(
	select ROW_NUMBER() over(PARTITION by userid order by ABS(datediff(day,getdate(),ordertime)) desc) as row,
	       userid,
	       ordertime
	from #t2
)

select b.NAME,a.ordertime
from cte a
join #t1 b on a.userid=b.id
where a.row=1

[kw13202]

引用 2 楼 xxfvba 的回复:

select username,ordertime from users a left join (select *,rn=row_number() over (partition by UserId order by ordertime) from Appointment where ordertime <=getdate()) b on a.Id=b.UserId and rn=1 where a.id is not null

这个只是找出最小的那条记录，不是最近的一条记录

[kw13202]

引用 5 楼 Landa_Ran 的回复:

刚才写错了

;with cte as
(
	select ROW_NUMBER() over(PARTITION by userid order by ABS(datediff(day,getdate(),ordertime)) desc) as row,
	       userid,
	       ordertime
	from #t2
)

select b.NAME,a.ordertime
from cte a
join #t1 b on a.userid=b.id
where a.row=1

这个只是找出最小的那条记录，不是最近的一条记录

[kw13202]

引用 1 楼 chinamobile851116 的回复:

CREATE TABLE #t1
(
id INT, 
NAME varchar(20)
)
INSERT INTO  #t1 VALUES(1,'a');
INSERT INTO  #t1 VALUES(2,'b');
INSERT INTO  #t1 VALUES(3,'c');

CREATE TABLE #t2
( id INT ,
userid INT ,
ordertime DATETIME
)
INSERT INTO  #t2 VALUES (1,1,'2015-07-30');
INSERT INTO  #t2 VALUES (2,1,'2015-07-23');
INSERT INTO  #t2 VALUES (3,2,'2015-07-26');
INSERT INTO  #t2 VALUES (4,1,'2015-07-31');
INSERT INTO  #t2 VALUES (5,2,'2015-07-21');
INSERT INTO  #t2 VALUES (6,1,'2015-07-26')
SELECT * FROM 
(SELECT * FROM #t1) a
LEFT JOIN 
(
SELECT userid,MAX(d) AS rq FROM 
(
SELECT userid,MAX(ordertime) AS d FROM #t2	WHERE ordertime<GETDATE() GROUP BY userid
UNION ALL
SELECT userid,MIN(ordertime) AS d FROM #t2	WHERE ordertime>=GETDATE() GROUP BY userid
) x 
GROUP BY x.userid
) b
ON a.id=b.userid


DROP TABLE #t1,#t2

我想到的是这个最笨的办法

谢谢，你这所谓笨办法，我都没有想出来