求一条SQL有两个表A、B表A NO、col1、col2为PKNO 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
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
1 2010-06-16 08:00 a null为什么是null?而不是50?
1 2010-06-16 08:00 a null 是打错了,应该是50 查不到就是null
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 行)*/
-- 建模拟数据
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 行受影响)
*/
--修正一下
-- 建模拟数据
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 行受影响)
*/