sql写法
有四张表:
table1 table2
c_code,p_code,p_name p_code,pin_number,pin_date
c-1 0001 A 0001 324 2007-6-1
c-2 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
table4
c_code,c_name
c-1 name1
c-2 name2
怎样查询在一定时间内的如下结果:
p_code,p_name,pin_date,pin_number,pout_date,pout_number,c_name
0002 2007-6-1 222 2007-6-1 4334 name2
0001 2007-6-1 324 name1
0001 2007-6-4 3244 name1
0002 2007-6-3 433 name2
[解决办法]
create table t1(c_code varchar(10),p_code varchar(10),p_name varchar(10))
insert into t1 values( 'c-1 ', '0001 ', 'A ')
insert into t1 values( 'c-2 ', '0002 ', 'B ')
create table t2(p_code varchar(10),pin_number int,pin_date datetime)
insert into t2 values( '0001 ', 324, '2007-6-1 ')
insert into t2 values( '0002 ', 433, '2007-6-3 ')
insert into t2 values( '0002 ', 222, '2007-6-1 ')
create table t3(p_code varchar(10),pout_number int,pout_date datetime)
insert into t3 values( '0002 ', 4334, '2007-6-1 ')
insert into t3 values( '0001 ', 3244, '2007-6-4 ')
create table t4(c_code varchar(10),c_name varchar(10))
insert into t4 values( 'c-1 ', 'name1 ')
insert into t4 values( 'c-2 ', 'name2 ')
go
select isnull(m.p_code,n.p_code) p_code,isnull(m.p_name,n.p_name) p_name,isnull(m.pin_date,n.pin_date) pin_date,
isnull(m.pin_number,n.pin_number) pin_number,isnull(n.pout_date,m.pout_date) pout_date,isnull(n.pout_number,m.pout_number) pout_number ,
isnull(m.c_name,n.c_name) c_name from
(
select a.p_code,a.p_name,convert(varchar(10),b.pin_date,120) pin_date,cast(b.pin_number as varchar) pin_number,pout_date= ' ',pout_number= ' ',d.c_name
from t1 a , t2 b , t4 d where a.p_code = b.p_code and a.c_code = d.c_code
) m
full join
(
select a.p_code,a.p_name,pin_date = ' ',pin_number = ' ',convert(varchar(10),c.pout_date,120) pout_date,cast(c.pout_number as varchar) pout_number,d.c_name
from t1 a , t3 c , t4 d where a.p_code = c.p_code and a.c_code = d.c_code
) n
on m.p_code = n.p_code and m.pin_date = n.pout_date
order by m.p_code,m.p_name
drop table t1,t2,t3,t4
/*
p_code p_name pin_date pin_number pout_date pout_number c_name
------ ------ ---------- ---------- ---------- ----------- ----------
0001 A 2007-06-01 324 name1
0001 A 2007-06-04 3244 name1
0002 B 2007-06-01 222 2007-06-01 4334 name2
0002 B 2007-06-03 433 name2
（所影响的行数为 4 行）
*/
