表1
ID JE1 JE2
1 1500 2000
1 500 2000
2 300 900
2 600 900
3 30 40
3 8 40
3 2 40
表2
ID JE1 IDATE
1 1500 2010-8-21
1 500 2010-8-30
2 900 2010-8-16
3 30 2010-8-17
3 8 2010-8-20
3 2 2010-8-24
表1和表2由ID连接,如何得到这样的表3?感谢各位高手!
表3
ID JE IDATE
1 1500 2010-8-21
1 500 2010-8-30
2 300 2010-8-16
2 600 2010-8-16
3 30 2010-8-17
3 8 2010-8-20
3 2 2010-8-24
ID JE1 JE2
1 1500 2000
1 500 2000
2 300 900
2 600 900
3 30 40
3 8 40
3 2 40
表2
ID JE1 IDATE
1 1500 2010-8-21
1 500 2010-8-30
2 900 2010-8-16
3 30 2010-8-17
3 8 2010-8-20
3 2 2010-8-24
表1和表2由ID连接,如何得到这样的表3?感谢各位高手!
表3
ID JE IDATE
1 1500 2010-8-21
1 500 2010-8-30
2 300 2010-8-16
2 600 2010-8-16
3 30 2010-8-17
3 8 2010-8-20
3 2 2010-8-24
值没大规律
from 表1 a left join 表2 b
on a.id=b.id and a.je1=b.je1
if object_id('[A]') is not null drop table [A]
create table [A] (ID int,JE1 int,JE2 int)
insert into [A]
select 1,1500,2000 union all
select 1,500,2000 union all
select 2,300,900 union all
select 2,600,900 union all
select 3,30,40 union all
select 3,8,40 union all
select 3,2,40
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (ID int,JE1 int,IDATE datetime)
insert into [B]
select 1,1500,'2010-8-21' union all
select 1,500,'2010-8-30' union all
select 2,900,'2010-8-16' union all
select 3,30,'2010-8-17' union all
select 3,8,'2010-8-20' union all
select 3,2,'2010-8-24'select A.ID,A.JE2,B.IDate from
(select row_number() over(order by getdate()) rn,* from [A])A
join
(select row_number() over(order by getdate()) rn,* from [B]) B
On A.RN=B.RNID JE2 IDate
----------- ----------- -----------------------
1 2000 2010-08-21 00:00:00.000
1 2000 2010-08-30 00:00:00.000
2 900 2010-08-16 00:00:00.000
2 900 2010-08-17 00:00:00.000
3 40 2010-08-20 00:00:00.000
3 40 2010-08-24 00:00:00.000(6 行受影响)
if object_id('[A]') is not null drop table [A]
create table [A] (ID int,JE1 int,JE2 int)
insert into [A]
select 1,1500,2000 union all
select 1,500,2000 union all
select 2,300,900 union all
select 2,600,900 union all
select 3,30,40 union all
select 3,8,40 union all
select 3,2,40
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (ID int,JE1 int,IDATE datetime)
insert into [B]
select 1,1500,'2010-8-21' union all
select 1,500,'2010-8-30' union all
select 2,900,'2010-8-16' union all
select 3,30,'2010-8-17' union all
select 3,8,'2010-8-20' union all
select 3,2,'2010-8-24'select A.ID,B.JE1,B.IDate from
(select row_number() over(order by getdate()) rn,* from [A])A
join
(select row_number() over(order by getdate()) rn,* from [B]) B
On A.RN=B.RNID JE1 IDate
----------- ----------- -----------------------
1 1500 2010-08-21 00:00:00.000
1 500 2010-08-30 00:00:00.000
2 900 2010-08-16 00:00:00.000
2 30 2010-08-17 00:00:00.000
3 8 2010-08-20 00:00:00.000
3 2 2010-08-24 00:00:00.000(6 行受影响)
连接后,ID=2的两条记录会以NULL填充,这2个NULL如何用b表ID=2的IDATE代替.
ID JE IDATE
1 1500 2010-8-21
1 500 2010-8-30
2 300 2010-8-16
2 600 2010-8-16
3 30 2010-8-17
3 8 2010-8-20
3 2 2010-8-24
谢谢楼上各位帮助,谢谢!不过我要得到的是7条记录,需要和表1的记录数一致.