如何按客户统计余额

[e9451973]

khz0912 a,khz1001 b两张表，字段一样，按照表A的客户号统计，一个客户号对应多个数据纪律，月初余额 ycye，月末余额 ymye，sql怎么写？

[wwwwb]

贴记录及要求结果出来看看

[e9451973]

A表
a b c
3212 张三 10
3212 李四 20
3213 张三 20
3214 王五 10

B表
a b c
3212 张三 10
3212 李四 50
结果
3212 张三 0
3212 李四 -30
3213 张三 20
3214 王五 10

[ACMAIN_CHM]

select a,b,sum(c) from
(
select a,b,c from A表
union all
select a,b,-c from B表
) t
group by a,b

[wwwwb]

select a,b,sum(c) from
(
select a,b,c from A表
union all
select a,b,-1*c from B表
) aa group by a,b

[e9451973]

c的结果是:sum(b.c-a.c)

[ACMAIN_CHM]

select A表.a,A表.b,A表.c-B表.c
from A表 left join B表 on A表.a=B表.a

[e9451973]

不对！

[rcom10002]

SELECT X.a, X.b, (X.t - Y.t) t
FROM
(
SELECT A.a, A.b, SUM(A.c) t
FROM A
GROUP BY A.a, A.b
) X LEFT JOIN
(
SELECT B.a, B.b, SUM(B.c) t
FROM B
GROUP BY B.a, B.b
) Y ON (X.a = Y.a AND X.b = Y.b)

[zwbhanye]

引用 8 楼 rcom10002 的回复:

SELECT X.a, X.b, (X.t - Y.t) t
  FROM
(
SELECT A.a, A.b, SUM(A.c) t
  FROM A
GROUP BY A.a, A.b
) X LEFT JOIN
(
SELECT B.a, B.b, SUM(B.c) t
  FROM B
GROUP BY B.a, B.b
) Y ON (X.a = Y.a AND X.b = Y.b)

完成正确