首页 新闻 搜索 专区 学院

SQL语句新问题

0
悬赏园豆:30 [已解决问题] 解决于 2008-09-03 16:49

A表

ID Name  Time

1  123     2008-1-1

1  123     2008-1-2

1  123     2008-1-3

1  123     2008-1-4

1  123     2008-1-5

1  123     2008-1-6

 

B表

ID  V1  V2   V3  Time

1   0     0      0   2008-1-1

1   0     0      0   2008-1-2

1   0     0      0   2008-1-5

1   0     0      0   2008-1-10

查询结果:()为说明

a.ID Name  a.Time  V1  V2   V3   b.Time

1  123     2008-1-1                     2008-1-1(取B表中对应的数据)

1  123     2008-1-2                     2008-1-2(取B表中对应的数据)

1  123     2008-1-3                     2008-1-2(向下取B表中最近的数据)

1  123     2008-1-4                     2008-1-2(向下取B表中最近的数据)

1  123     2008-1-5                     2008-1-5(取B表中对应的数据)

1  123     2008-1-6                     2008-1-5(向下取B表中最近的数据)

A表中数据查询出来,再找与之对应B表中匹配时间相同的数据,时间不存在,则向下取最近的数据?

 

想了一上午没法解决?现在怀疑能不能用SQL写出来

PS:

不能写:

select A.*,

(select top 1 v1 from B where B.ID=A.ID and B.time<=A.Time order by B.Time desc) as V1

from A

这样的语句

 

liy的主页 liy | 初学一级 | 园豆:30
提问于:2008-09-03 16:14
< >
分享
最佳答案
0
select A.ID, A.Name, A.[Time] as ATime, B.V1, B.V2, B.V3, B.[Time] as BTime from A inner join B on B.[Time] = (select MAX([Time]) from B ttb where ttb.[Time] <= A.[Time])
Gray Zhang | 专家六级 |园豆:17610 | 2008-09-03 16:20
其他回答(2)
0
不错,支持 Gray Zhang
一杰 | 园豆:790 (小虾三级) | 2008-09-03 16:50
0
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 ......
roboth | 园豆:28 (初学一级) | 2008-09-03 17:13
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册