HELP

[sun139]

Table1数据
--------------------------------------------------------
pid pno name
--------------------------------------------------------
2876 200010001 汇源果鲜美橙汁
2877 200010002 汇源果鲜美猕猴桃汁
2878 200010003 汇源果鲜美桃汁
2881 200010006 麒麟鲜橙汁
2882 200010007 汇源迪士尼橙汁
2883 200010008 汇源迪士尼桃汁
2884 200010009 汇源迪士尼苹果汁
2885 200010010 汇源迪士尼葡萄汁
2887 200010012 三得利鲜橙汁
2888 200010013 三得利鲜橙汁




Table2数据
--------------------------------------------------------
mno date
--------------------------------------------------------
20070100125 2007-01-31
20070200136 2007-02-28
20070300178 2007-03-31



Table3数据
--------------------------------------------------------
no mno
--------------------------------------------------------
410619340010037 20070100125
410619340010038 20070200136
410619350010039 20070300178



Table4数据
--------------------------------------------------------
no pid qty slamt
--------------------------------------------------------
410619340010037 2876 78 2.3
410619340010038 2877 85 3.5
410619350010039 2878 98 7.8
---------------------------------------------------------------------

select * from
( select * from table1 a,table2 b,table3 c,table4 d
where left(a.pno,3)in(200) and a.pid=d.pid and b.mno=c.mno and c.no=d.no
and b.date='2007-01-31'
) bb full outer join
(select * from table1 a1,table2 b1,table3 c1,table4 d1
where left(a1.pno,3)in(200) and a1.pid=d1.pid and b1.mno=c1.mno and c1.no=d1.no
and b1.date='2007-02-28'
) dd on bb.pid=dd.pid


希望得到的数据是
------------------------------------------------------------------
pid pno name dd.qty dd.slamt bb.qty bb.slamt
------------------------------------------------------------------
2876 200010001 鲜美橙汁 15 2.3 NULL NULL
2877 200010002 猕猴桃汁 10 3.5 NULL NULL
2878 200010003 鲜美桃汁 NULL NULL 5 5.5
2881 200010006 麒麟鲜橙汁 10 5.5 NULL NULL
2882 200010007 迪士尼橙汁 15 6.5 NULL NULL
2883 200010008 迪士尼桃汁 NULL NULL 25 7.5
2884 200010009 迪士尼苹果汁 NULL NULL 5 8.5
2885 200010010 迪士尼葡萄汁 NULL NULL 10 5.0

[sun139]

老大们.怎么就没人看啊.

[zhengzeng]

先顶顶

[marco08]

--try

create table T1(pid int, pno varchar(50), name varchar(50))
insert T1 select 2876, '200010001', '汇源果鲜美橙汁'
union all select 2877, '200010002', '汇源果鲜美猕猴桃汁'
union all select 2878, '200010003', '汇源果鲜美桃汁'
union all select 2881, '200010006', '麒麟鲜橙汁'
union all select 2882, '200010007', '汇源迪士尼橙汁'
union all select 2883, '200010008', '汇源迪士尼桃汁'
union all select 2884, '200010009', '汇源迪士尼苹果汁'
union all select 2885, '200010010', '汇源迪士尼葡萄汁'
union all select 2887, '200010012', '三得利鲜橙汁'
union all select 2888, '200010013', '三得利鲜橙汁'

create table T2(mno varchar(50), [date] varchar(10))
insert T2 select '20070100125', '2007-01-31'
union all select '20070200136', '2007-02-28'
union all select '20070300178', '2007-03-31'


create table T3(no varchar(50), mno varchar(50))
insert T3 select '410619340010037', '20070100125'
union all select '410619340010038', '20070200136'
union all select '410619350010039', '20070300178'

create table T4(no varchar(50), pid int, qty int, slamt decimal(10,1))
insert T4 select '410619340010037', 2876, 78, 2.3
union all select '410619340010038', 2877, 85, 3.5
union all select '410619350010039', 2878, 98, 7.8

select pid=isnull(bb.pid, dd.pid),
pno=isnull(bb.pno, dd.pno),
name=isnull(bb.name, dd.name),
[dd.qty]=dd.qty,
[dd.slmat]=dd.slamt,
[bb.qty]=bb.qty,
[bb.slamt]=bb.slamt
from
(
select a.*, d.qty, d.slamt from T1 a, T2 b, T3 c, T4 d
where left(a.pno,3)='200' and a.pid=d.pid and b.mno=c.mno and c.no=d.no and b.[date]='2007-03-31'
)
bb full join
(
select a.*, d.qty, d.slamt from T1 a, T2 b, T3 c, T4 d
where left(a.pno,3)='200' and a.pid=d.pid and b.mno=c.mno and c.no=d.no and b.[date]='2007-02-28'
)dd on bb.pid=dd.pid

[marco08]

--result
pid pno name dd.qty dd.slmat bb.qty bb.slamt
----------- -------------------------------------------------- -------------------------------------------------- ----------- ------------ ----------- ------------
2877 200010002 汇源果鲜美猕猴桃汁 85 3.5 NULL NULL
2878 200010003 汇源果鲜美桃汁 NULL NULL 98 7.8

(2 row(s) affected)

[sun139]

恩.谢谢了.