A表:
matercode1 maternum1 materindate1 depot1
1001 5 2007-05-07 5
1002 6 2007-05-07 5
B表
matercode2 maternum2 materindate2 depot2
1001 6 2007-05-07 5
如何使查询结果为
matercode1 maternum1 MaterNum2
1001 5 6
1002 6 0
也就是说.相同的matercode中的maternum要在一条上显示.如果没有的.则显示为0
matercode1 maternum1 materindate1 depot1
1001 5 2007-05-07 5
1002 6 2007-05-07 5
B表
matercode2 maternum2 materindate2 depot2
1001 6 2007-05-07 5
如何使查询结果为
matercode1 maternum1 MaterNum2
1001 5 6
1002 6 0
也就是说.相同的matercode中的maternum要在一条上显示.如果没有的.则显示为0
select matercode1,maternum1,
MaterNum2=(select isnull(sum(maternum2),0) from B where matercode2=A.matercode1)
from A
insert A select 1001, 5, '2007-05-07', 5
union all select 1002, 6, '2007-05-07', 5create table B(matercode2 int,maternum2 int,materindate2 datetime,depot2 int)
insert B select 1001, 6, '2007-05-07', 5select matercode1,maternum1,
MaterNum2=(select isnull(sum(maternum2),0) from B where matercode2=A.matercode1)
from A--result
matercode1 maternum1 MaterNum2
----------- ----------- -----------
1001 5 6
1002 6 0(2 row(s) affected)