请教个问题关于spl的
有三张表:
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
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 4334
0001 2007-6-1 324
0001 2007-6-4 3244
0002 2007-6-3 433
[解决办法]
select a.p_code,cast(varchar(10),b.pin_date,120) pin_date,b.pin_number , pout_date = ' ',pout_number = 0
from table1 a, table2 b where a.p_code = b.p_code
union all
select a.p_code, pin_date = ' ', pin_number = 0 , cast(varchar(10),pout_date,120) pout_date , pout_number
from table1 a, table3 b where a.p_code = b.p_code
[解决办法]
create table table1(p_code varchar(4),p_name varchar(1))
insert into table1 values( '0001 ', 'A ' )
insert into table1 values( '0002 ', 'B ' )
create table table2(p_code varchar(4),pin_number int,pin_date datetime)
insert into table2 values( '0001 ', 324, '2007-6-1 ' )
insert into table2 values( '0002 ', 433, '2007-6-3 ')
create table table3(p_code varchar(4),pout_number int,pout_date datetime)
insert into table3 values( '0002 ', 4334, '2007-6-1 ' )
insert into table3 values( '0001 ', 3244, '2007-6-4 ')
go
select a.p_code,convert(varchar(10),b.pin_date,120) pin_date,b.pin_number , pout_date = ' ',pout_number = 0
from table1 a, table2 b where a.p_code = b.p_code
union all
select a.p_code, pin_date = ' ', pin_number = 0 , convert(varchar(10),pout_date,120) pout_date , pout_number
from table1 a, table3 b where a.p_code = b.p_code
order by a.p_code,a.pin_date
drop table table1,table2,table3
/*
p_code pin_date pin_number pout_date pout_number
------ ---------- ----------- ---------- -----------
0001 0 2007-06-04 3244
0001 2007-06-01 324 0
0002 0 2007-06-01 4334
0002 2007-06-03 433 0
（所影响的行数为 4 行）
*/
