表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
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
解决方案 »
- html
- 如果取得Server的全部视图,包括视图名和视图脚本?
- 急,怎么在winXP上就连不上?用相同的数据库,相同的代码在别人机器win2000上好使.
- 求触发器写法?
- 数据库复制和镜像共同使用的问题
- 求一SQL语句,请高手赐教!
- vc用ADO方式对SQL SERVER删除操作时 为什么会删除多行?
- SQL触发器的使用!!
- 遇到储存过程的问题!
- 我建了一个作业"modifydate",但在查询分析器运行USE cass EXEC sp_delete_job @job_name = 'modifydate',出现未能找到存储过程 'sp_delet
- 如何通过一条SQL语句得到如下效果,在线等!!!!!
- 存储过程中写触发器并调用,改怎么写,,?
insert into a values(1, 0.05, '2006', '2007')
insert into a values(2, 0.1 , '2007', '2008')
create table b (A1 int, B1 varchar(6))
insert into b values(1, '2004.5')
insert into b values(2, '2005.5')
insert into b values(3, '2006.5')
insert into b values(4, '2007.5')
select b.* , a.b from b left join a on left(b.b1,4) = a.cdrop table a,b/*
A1 B1 b
----------- ------ --------------------
1 2004.5 NULL
2 2005.5 NULL
3 2006.5 .05
4 2007.5 .10(所影响的行数为 4 行)
*/
insert into a values(1, 0.05, '2006', '2007')
insert into a values(2, 0.1 , '2007', '2008')
create table b (A1 int, B1 varchar(6))
insert into b values(1, '2004.5')
insert into b values(2, '2005.5')
insert into b values(3, '2006.5')
insert into b values(4, '2007.5')
select b.* , isnull(cast(a.b as varchar),'') b from b left join a on left(b.b1,4) = a.cdrop table a,b/*
A1 B1 b
----------- ------ ------------------------------
1 2004.5
2 2005.5
3 2006.5 0.05
4 2007.5 0.10(所影响的行数为 4 行)*/
谢谢热心帮忙 嘿嘿 不过你总是要用年份来相等不对....哎也许我没有说明白吧
A B C D
1 0.05 2006/01/01 2007/01/01
2 0.1 2007/01/01 2008/01/01
表2
A1 B1
1 2004/03/01
2 2005/03/01
3 2006/03/01
4 2007/03/01
恩这接近原始表了
insert into a values(1, 0.05, '2006', '2007')
insert into a values(2, 0.1 , '2007', '2008')
create table b (A1 int, B1 varchar(6))
insert into b values(1, '2004.5')
insert into b values(2, '2005.5')
insert into b values(3, '2006.5')
insert into b values(4, '2007.5')
select b.* , isnull(cast(a.b as varchar),'') b from b left join a on b.b1 >= a.c and b.b1 <= a.ddrop table a,b/*
A1 B1 b
----------- ------ ------------------------------
1 2004.5
2 2005.5
3 2006.5 0.05
4 2007.5 0.10(所影响的行数为 4 行)*/
/*
A1 B1 b
----------- ------ ------------------------------
1 2004.5
2 2005.5
3 2006.5 0.05
4 2007.5 0.10(所影响的行数为 4 行)*/
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, 2008insert into test2
select 1, 2004.5 union
select 2, 2005.5 union
select 3, 2006.5 union
select 4 , 2007.5select * from test1
select * from test2select 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
表2.b1 >= 表1.c and 表2.b1 <= 表1.d这是你的意思吧?
恩 就是这个意思
create table a (A int,B decimal(18,2),C varchar(4),D varchar(4))
insert into a values(1, 0.05, '2006', '2007')
insert into a values(2, 0.1 , '2007', '2008')
create table b (A1 int, B1 varchar(6))
insert into b values(1, '2004.5')
insert into b values(2, '2005.5')
insert into b values(3, '2006.5')
insert into b values(4, '2007.5')
select b.* , isnull(cast(a.b as varchar),'') b from b left join a on b.b1 >= a.c and b.b1 <= a.ddrop table a,b/*
A1 B1 b
----------- ------ ------------------------------
1 2004.5
2 2005.5
3 2006.5 0.05
4 2007.5 0.10(所影响的行数为 4 行)
*/
---创建测试
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 行)
*/
from b left join a on b.b1 between a.c and a.d