SQL语句新问题

Question

A表ID Name  Time1  123     2008-1-11  123     2008-1-21  123     2008-1-31  123     2008-1-41  123     2008-1-51  123     2008-1-6 B表ID  V1  V2   V3  Time1   0     0      0   2008-1-11   0     0      0   2008-1-21   0     0      0   2008-1-51   0     0      0   2008-1-10查询结果：（）为说明a.ID Name  a.Time  V1  V2   V3   b.Time1  123     2008-1-1                     2008-1-1（取Ｂ表中对应的数据）1  123     2008-1-2                     2008-1-2（取Ｂ表中对应的数据）1  123     2008-1-3                     2008-1-2（向下取Ｂ表中最近的数据）1  123     2008-1-4                     2008-1-2（向下取Ｂ表中最近的数据）1  123     2008-1-5                     2008-1-5（取Ｂ表中对应的数据）1  123     2008-1-6                     2008-1-5（向下取Ｂ表中最近的数据）Ａ表中数据查询出来，再找与之对应Ｂ表中匹配时间相同的数据，时间不存在，则向下取最近的数据？ 想了一上午没法解决？现在怀疑能不能用ＳＱＬ写出来ＰＳ：不能写：select A.*,(select top 1 v1 from B where B.ID=A.ID and B.time<=A.Time order by B.Time desc) as V1from A这样的语句

Answer 1

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 ......