求sql如何连接2个不同关键字求和结果

[unicornhx]

如表#T 列名为 A B a_b b_a
A，B分别是两个参数的ID， a_b表示由A求得B的值，b_a表示由B求得A的值
现在想将 select sum(a_b) from #T group by A 和 select sum（b_a） from #T group by B 两个表连接显示成 A B sum（a_b） sum（b_a) 怎么写join语句？ 谢谢了

[fengxiaohan211]

select A,sum(a_b) from #T group by A union select B,sum（b_a） from #T group by B

[發糞塗牆]

SELECT  MAX(A) A ,
        MAX(B) B ,
        SUM(suma_b) suma_b ,
        SUM(sumb_a) sumb_a
FROM    ( SELECT    A ,
                    NULL B ,
                    SUM(a_b) suma_b ,
                    0 sumb_a
          FROM      #T
          GROUP BY  A
          UNION
          SELECT    NULL A ,
                    B ,
                    0 suma_b ,
                    SUM(b_a) sumb_a
          FROM      #T
          GROUP BY  B
        ) A

[hnhychenhui]

select * from(select A,sum(a_b) as sum_a from dbo.test group by A) as t1 left join (select * from (select B,sum(b_a) as sum_b from dbo.test group by B) as t2) as t3 on t1.A=t3.B;

[unicornhx]

方法错误，union连接的是行连接，结果是俩个表合成一个两列的表，我的需求是A|B|sum(A)|sum(B) A,B都是不同集合，混在一列完全错误了..

引用 1 楼 fengxiaohan211 的回复:

select A,sum(a_b) from #T group by A union select B,sum（b_a） from #T group by B

[unicornhx]

你的方法正确~ 我的写法是这样 select x1.A ，t1 as sum(A),t2 as sum B from (select A,sum(A) as t1 from #T group by A) as x1 join (select B,sum( B) as t2) from #T group by B) as x2 on x2.A=x1.B

引用 3 楼 hnhychenhui 的回复:

select * from(select A,sum(a_b) as sum_a from dbo.test group by A) as t1 left join (select * from (select B,sum(b_a) as sum_b from dbo.test group by B) as t2) as t3 on t1.A=t3.B; ……