在线等一条SQL
表1
A B C D
1 0.05 2006 2007
2 0.1 2007 2008
表2
A1 B1
1 2004.5
2 2005.5
3 2006.5
4 2007.5
A1 B1 B
1 2004.5
2 2005.5
3 2006.5 0.05
4 2007.5 0.1
[解决办法]
select 表2.* , 表1.b from 表2 left 表1 on left(表2.b1,4) = 表1.c
[解决办法]
if object_id( 'test1 ') is not null
drop table test1
create table test1(
a int,
b float,
c int,
d int
)
if object_id( 'test2 ') is not null
drop table test2
create table test2(
a1 int,
b1 float
)
insert into test1
select 1, 0.05, 2006, 2007 union
select 2, 0.1, 2007, 2008
insert into test2
select 1, 2004.5 union
select 2, 2005.5 union
select 3, 2006.5 union
select 4 , 2007.5
select * from test1
select * from test2
select b.* ,B1=mAX(case when b.b1> a.c and b.b1 <a.d then
a.b else null end) from test2 b , test1 a
GROUP by b.a1,b.b1
[解决办法]
---你表1中C和D列是什么类型???
---创建测试
Declare @表1 Table(A int,B decimal(8,2),C datetime,D datetime)
Insert @表1 Select 1,0.05, '2006-01-01 ', '2007-01-01 '
Union All Select 2,0.1 , '2007-01-01 ', '2008-01-01 '
Declare @表2 Table(A1 int,B1 datetime)
Insert @表2 Select 1, '2004-03-01 '
Union All Select 2, '2005-03-01 '
Union All Select 3, '2006-03-01 '
Union All Select 4, '2007-03-01 '
Select * From @表1
Select * From @表2
---查询结果
Select
A.*,B.B
From
@表2 As A
Left Join
@表1 As B
On A.B1> =B.C And A.B1 <=B.D
--结果
/*
A1 B1 B
----------- ------------------------ ----------
1 2004-03-01 00:00:00.000 NULL
2 2005-03-01 00:00:00.000 NULL
3 2006-03-01 00:00:00.000 .05
4 2007-03-01 00:00:00.000 .10
（所影响的行数为 4 行）
*/
[解决办法]
select b.* , isnull(cast(a.b as varchar), ' ') b
from b left join a on b.b1 between a.c and a.d
