a表1a Ea 20 out in
1a Ea 100 after in
1b Ea 50 out and
1c Eb 100 on in
1c Eb 50 out in
1d Ed 100 by in
1d Ef 50 to for
1d Ea 10 out in
1a Ea 100 out left
NULL NULL NULL NULL NULL
b 表
1a Ea 2 out in
1a Ea 50 after in
1s cc 5 out and
1c Eb 10 on in
1c Eb 50 out in
1t Em 10 by in
1d Ef 100 to for
1d Ea 100 out in
1a Ea 10 out left
NULL NULL NULL NULL NULL现在要得到 的结果
两个表分别按照 coa1 coa2 和co1b,cob2 分组
聚合sum quantity1 和quantity'2
两个表连接
FULL JOIN
ON a.coa1=b.cob1AND a.coa2=b.cob2ON a.coa1=b.cob1AND a.coa2=b.cob2
结果要得到列coa1,coa2,sumquantitya,cob1,cob2,sumquantityb,namea1,namea2,nameb1,nameb2
其中name各个列不要分组取第一条记录,就像access的first函数
1a Ea 100 after in
1b Ea 50 out and
1c Eb 100 on in
1c Eb 50 out in
1d Ed 100 by in
1d Ef 50 to for
1d Ea 10 out in
1a Ea 100 out left
NULL NULL NULL NULL NULL
b 表
1a Ea 2 out in
1a Ea 50 after in
1s cc 5 out and
1c Eb 10 on in
1c Eb 50 out in
1t Em 10 by in
1d Ef 100 to for
1d Ea 100 out in
1a Ea 10 out left
NULL NULL NULL NULL NULL现在要得到 的结果
两个表分别按照 coa1 coa2 和co1b,cob2 分组
聚合sum quantity1 和quantity'2
两个表连接
FULL JOIN
ON a.coa1=b.cob1AND a.coa2=b.cob2ON a.coa1=b.cob1AND a.coa2=b.cob2
结果要得到列coa1,coa2,sumquantitya,cob1,cob2,sumquantityb,namea1,namea2,nameb1,nameb2
其中name各个列不要分组取第一条记录,就像access的first函数
还有a表中的name要取对应的第一条记录,就像就像access的first函数。
也不知道怎么放进去,高人请指点。SELECT coa1,coa2,sum(quantitya) AS sumquantitya,sum(y.sumquantityb)
AS sumquantitya FROM aFULL JOIN (SELECT cob1,cob2,sum(quantityb) AS sumquantityb FROM b
GROUP BY cob1,cob2) AS y
ON a.coa1=y.cob1AND a.coa2=y.cob2
GROUP BY a.coa1,a.coa2coa1 coa2 sumquantitya sumquantityb
NULL NULL NULL 15
1a Ea 220 186
1b Ea 50 NULL
1d Ea 10 100
1c Eb 150 120
1d Ed 100 NULL
1d Ef 50 100
from (
select a1.coa1,a1.coa2,sum(a1.QuantityA) as sumquantitya,(select top 1 namea1 from a where coa1=a1.coa1 and coa2=a1.coa2) as namea1,(select top 1 namea2 from a where
coa1=a1.coa1 and coa2=a1.coa2) as namea2
from a a1
group by a1.coa1,a1.coa2
) as a2 full join (
select b1.cob1,b1.cob2,sum(b1.QuantityB) as sumquantityb,(select top 1 nameb1 from b where cob1=b1.cob1 and cob2=b1.cob2) as nameb1,(select top 1 nameb2 from b where
cob1=b1.cob1 and cob2=b1.cob2) as nameb2
from b b1
group by b1.cob1,b1.cob2
) as b2
on a2.coa1=b2.cob1
AND a2.coa2=b2.cob2