如题,我要做两列分别的加和,但是一个可以取到两个值,一个只能取到一个,可是在加和的时候,那个取到一个的会加两遍,怎么去掉呢?
no currency money
a 01 10
b 01 52
c 02 20当我做select sum(a.money),sum(b.money) from label p left join label a on p.no=a.no and
a.currency='01' left join label b on p.no=b.no and b.currency='02' group by no;
得出的结果是62,40;可是应是62,20阿.怎么解决呢?
no currency money
a 01 10
b 01 52
c 02 20当我做select sum(a.money),sum(b.money) from label p left join label a on p.no=a.no and
a.currency='01' left join label b on p.no=b.no and b.currency='02' group by no;
得出的结果是62,40;可是应是62,20阿.怎么解决呢?
[02]=(select sum(money) from label where currency='02'
from label
结果应该是
01 62
02 20其实结果已经出来了,非要显示成
01 02
62 20
用行转列就是了。
如果 currency的值不确定要动态转,如果只是固定的几个值,那么用CASE WHEN或楼上(chuifengde)的写法比如
select [01]=sum(case when currency='01' then money else 0 end),
[02]=sum(case when currency='02' then money else 0 end)
from tb group by currency