求一条SQL

[一卷阳光]

求一条SQL

有两个表A、B

表A NO、col1、col2为PK

NO col1 col2 col3
1 2010-06-16 08:00 a
2 2010-06-17 08:00 b
3 2010-06-18 09:00 c
3 2010-06-18 10:00 d
4 2010-06-18 10:00 d


表B NO、col1、col2为PK
NO col1 col2 col3
1 2010-06-16 08:00 50
2 2010-06-17 08:00 60
3 2010-06-18 07:00 70
3 2010-06-18 08:00 80
3 2010-06-18 09:00 90

要求查询出
A表资料及与A表中日期、NO相同
且小于等于A表时间的 B表col3

例如：

1 2010-06-16 08:00 a null
2 2010-06-17 08:00 b 60
3 2010-06-18 09:00 c 80
3 2010-06-18 10:00 d 90
4 2010-06-18 10:00 d null

[jwdream2008]

要求描述不清晰！

[一卷阳光]

补充：且小于等于A表时间的 B表col3

时间要最接近A表时间的 B表col3

[一卷阳光]

哪里不清楚？

[chuifengde]

看不懂结果

[TheGodOfGods]

1 2010-06-16 08:00 a null

为什么是null？而不是50？

[一卷阳光]

1 2010-06-16 08:00 a null 是打错了，应该是50 查不到就是null

[一卷阳光]

请各位大哥帮忙再看看

[shixixi1987]

你那个年月日跟时分时分开的吗？

[chuifengde]

DECLARE @a TABLE(NO INT, col1 VARCHAR(10), col2 VARCHAR(7), col3 CHAR(1))

insert @a select 1 ,'2010-06-16','08:00' ,'a'
union all select 2 ,'2010-06-17','08:00' ,'b'
union all select 3 ,'2010-06-18','09:00' ,'c'
union all select 3 ,'2010-06-18','10:00' ,'d'
union all select 4 ,'2010-06-18','10:00' ,'d'

DECLARE @b TABLE(NO INT, col1 VARCHAR(10), col2 VARCHAR(7), col3 int)
INSERT @b SELECT 1 ,'2010-06-16', '08:00', 50
union all select 2 ,'2010-06-17', '08:00', 60
union all select 3 ,'2010-06-18', '07:00', 70
union all select 3 ,'2010-06-18', '08:00', 80
union all select 3 ,'2010-06-18', '09:00', 90


SELECT *,(SELECT TOP 1 col3 FROM @b WHERE no=a.no AND col1=a.col1 AND col2<=a.col2 ORDER BY col2 desc)
FROM @a a
--result
/*NO          col1       col2    col3             
----------- ---------- ------- ---- ----------- 
1           2010-06-16 08:00   a    50
2           2010-06-17 08:00   b    60
3           2010-06-18 09:00   c    90
3           2010-06-18 10:00   d    90
4           2010-06-18 10:00   d    NULL

（所影响的行数为 5 行）

*/ 

[shixixi1987]

不同字段

[rmljoe]

-- 豆子
-- 建模拟数据
create table tb_A (NO int,col1 varchar(10),col2 varchar(5),col3 varchar(5))
go
insert into tb_A select 1,'2010-06-16','08:00','a'
       union all select 2,'2010-06-17','08:00','b'
       union all select 3,'2010-06-18','09:00','c'
       union all select 3,'2010-06-18','10:00','d'
       union all select 4,'2010-06-18','10:00','d'
go
create table tb_B (NO int,col1 varchar(10),col2 varchar(5),col3 int)
go
insert into tb_B select 1,'2010-06-16','08:00',50
       union all select 2,'2010-06-17','08:00',60
       union all select 3,'2010-06-18','07:00',70
       union all select 3,'2010-06-18','08:00',80
       union all select 3,'2010-06-18','09:00',90
go


-- 测试
select 
NO,
col1,
col2,
(select top 1 b.col3 from tb_B b where a.col1 = b.col1 and a.no = b.no and b.col2 < a.col2 order by b.col2 desc)
from tb_A a
 
-- 结果
/*
NO          col1       col2  
----------- ---------- ----- -----------
1           2010-06-16 08:00 NULL
2           2010-06-17 08:00 NULL
3           2010-06-18 09:00 80
3           2010-06-18 10:00 90
4           2010-06-18 10:00 NULL

(5 行受影响)
*/ 

[rmljoe]

--修正一下
-- 建模拟数据
create table tb_A (NO int,col1 varchar(10),col2 varchar(5),col3 varchar(5))
go
insert into tb_A select 1,'2010-06-16','08:00','a'
       union all select 2,'2010-06-17','08:00','b'
       union all select 3,'2010-06-18','09:00','c'
       union all select 3,'2010-06-18','10:00','d'
       union all select 4,'2010-06-18','10:00','d'
go
create table tb_B (NO int,col1 varchar(10),col2 varchar(5),col3 int)
go
insert into tb_B select 1,'2010-06-16','08:00',50
       union all select 2,'2010-06-17','08:00',60
       union all select 3,'2010-06-18','07:00',70
       union all select 3,'2010-06-18','08:00',80
       union all select 3,'2010-06-18','09:00',90
go


-- 测试
select 
NO,
col1,
col2,
(select top 1 b.col3 from tb_B b where a.col1 = b.col1 and a.no = b.no and b.col2 <= a.col2 order by b.col2 desc) as col3
from tb_A a
 
-- 结果
/*
NO          col1       col2  col3
----------- ---------- ----- -----------
1           2010-06-16 08:00 50
2           2010-06-17 08:00 60
3           2010-06-18 09:00 90
3           2010-06-18 10:00 90
4           2010-06-18 10:00 NULL

(5 行受影响)
*/ 

[一卷阳光]

多谢各位大哥

[一卷阳光]

不好意思，第一次结贴给错分了。多谢rmljoe & chuifengde