有三张表:
table1 table2
p_code,p_name p_code,pin_number,pin_date
0001 A 0001 324 2007-6-1
0002 B 0002 433 2007-6-3
0002 222 2007-6-1
table3
p_code,pout_number,pout_date
0002 4334 2007-6-1
0001 3244 2007-6-4怎样输出:
p_code,pin_date,pin_number,pout_date,pout_number
0002 2007-6-1 222 2007-6-1 4334
0001 2007-6-1 324
0001 2007-6-4 3244
0002 2007-6-3 433
table1 table2
p_code,p_name p_code,pin_number,pin_date
0001 A 0001 324 2007-6-1
0002 B 0002 433 2007-6-3
0002 222 2007-6-1
table3
p_code,pout_number,pout_date
0002 4334 2007-6-1
0001 3244 2007-6-4怎样输出:
p_code,pin_date,pin_number,pout_date,pout_number
0002 2007-6-1 222 2007-6-1 4334
0001 2007-6-1 324
0001 2007-6-4 3244
0002 2007-6-3 433
drop table test1
create table test1(
P_id int ,
P_name varchar(10)
)
if object_id('test2') is not null
drop table test2
create table test2(
P_id int,
Pin_num int,
Pin_date varchar(10)
)
if object_id('test3') is not null
drop table test3
create table test3(
P_id int,
Pout_num int,
Pout_date varchar(10)
)insert into test1
select 1,'a' union
select 2,'b'insert into test2
select 1,324,'2007-6-1' union
select 2,433,'2007-6-3' union
select 2,222,'2007-6-1'insert into test3
select 2,4334,'2007-6-1' union
select 1,3244,'2007-6-4'select distinct * from (
select d.p_id,pin_num,pin_date,pout_num,pout_date from (select a.p_id,pin_num,pin_date from
test1 a left join test2 b on a.p_id=b.p_id) d left join
test3 c on c.p_id=d.p_id and d.pin_date= c.pout_date
union
select d.p_id,pin_num,pin_date,pout_num,pout_date from (select a.p_id,pout_num,pout_date from
test1 a left join test3 b on a.p_id=b.p_id) d left join
test2 c on c.p_id=d.p_id and d.pout_date= c.pin_date) e
test1 a left join test2 b on a.p_id=b.p_id) d left join
test3 c on c.p_id=d.p_id and d.pin_date= c.pout_date
union
select d.p_id,pin_num,pin_date,pout_num,pout_date from (select a.p_id,pout_num,pout_date from
test1 a left join test3 b on a.p_id=b.p_id) d left join
test2 c on c.p_id=d.p_id and d.pout_date= c.pin_date
test1 a left join test2 b on a.p_id=b.p_id) 用d