2 回答
TA贡献1797条经验 获得超6个赞
DECLARE @a table(id int ,[name] varchar(10), [time] datetime) DECLARE @b table(id int ,v1 int ,v2 int, v3 int ,[time] datetime) INSERT INTO @a SELECT 1, '123', '2008-1-1' UNION ALL SELECT 1, '123', '2008-1-2' UNION ALL SELECT 1, '123', '2008-1-3' UNION ALL SELECT 1, '123', '2008-1-4' UNION ALL SELECT 1, '123', '2008-1-5' UNION ALL SELECT 1, '123', '2008-1-6' INSERT INTO @B SELECT 1, 0, 0, 0, '2008-1-1' UNION ALL SELECT 1, 0, 0, 0, '2008-1-2' UNION ALL SELECT 1, 0, 0, 0, '2008-1-5' UNION ALL SELECT 1, 0, 0, 0, '2008-1-10' --METHOD 1 SELECT a.*, (SELECT MAX(b.[time]) FROM @b b where b.id=a.id and DATEDIFF(day,a.[time],b.[time])<=0 ) as [time] FROM @a a --METHOD2 SELECT a.*, (SELECT TOP 1 b.[time] FROM @b b where b.id=a.id and DATEDIFF(day,a.[time],b.[time])<=0 ORDER BY b.[time] DESC ) as [time] FROM @a a --METHOD3 ......
添加回答
举报