表里边的这样几个域
org_id,fund_id,amt
org_id有1,2,3,4,...15这样几个,fund_id有a,b,c....等很多
现在想统计出这样一个表
fund_id |1 |2 |3 |...
-----------------------------------------------------------------------------
a | fund_id=a,org_id=1时的sum(amt) |a,2,sum(amt) | a,3,sum(amt) |
-----------------------------------------------------------------------------
b | fund_id=b,org_id=1时的sum(amt) |b,2,sum(amt) |b,3,sum(amt) |
-----------------------------------------------------------------------------
..............大概知道用sum(decode(org_id,1,amt,0)),
这个只能统计一里边的那一块,整个的好像要嵌套?
哪位HELP一下。。
谢谢。。
org_id,fund_id,amt
org_id有1,2,3,4,...15这样几个,fund_id有a,b,c....等很多
现在想统计出这样一个表
fund_id |1 |2 |3 |...
-----------------------------------------------------------------------------
a | fund_id=a,org_id=1时的sum(amt) |a,2,sum(amt) | a,3,sum(amt) |
-----------------------------------------------------------------------------
b | fund_id=b,org_id=1时的sum(amt) |b,2,sum(amt) |b,3,sum(amt) |
-----------------------------------------------------------------------------
..............大概知道用sum(decode(org_id,1,amt,0)),
这个只能统计一里边的那一块,整个的好像要嵌套?
哪位HELP一下。。
谢谢。。
where org_id=1 and fund_id in('A','B')
group by fund_id
如果不固定啊,那估计得用过程去写了.
sum(decode(org_id, 1, amt, 0)) 1,
sum(decode(org_id, 2, amt, 0)) 2,
sum(decode(org_id, 3, amt, 0)) 3,
sum(decode(org_id, 4, amt, 0)) 4,
sum(decode(org_id, 5, amt, 0)) 5,
sum(decode(org_id, 6, amt, 0)) 6,
sum(decode(org_id, 7, amt, 0)) 7,
sum(decode(org_id, 8, amt, 0)) 8,
sum(decode(org_id, 9, amt, 0)) 9,
sum(decode(org_id, 10, amt, 0)) 10,
sum(decode(org_id, 11, amt, 0)) 11,
sum(decode(org_id, 12, amt, 0)) 12,
sum(decode(org_id, 13, amt, 0)) 13,
sum(decode(org_id, 14, amt, 0)) 14,
sum(decode(org_id, 15, amt, 0)) 15
from t
group by fund_id
这么写SQL select fund_id,sum(decode(org_id,1,amt,0)) as 1, .....,sum(decode(org_id,1,amt,0)) as 15
from yourtable
group by fund_id
你所说的报错是这种情况:
select fund_id,org_id,.... from test group by fund_id 使用汇总函数对org_id进行运算,在select 里没问题的.
都上来好像就要decode嵌套了。
马上去编辑主贴。
域还要加上一列act,有1,2,3三种操作org_id,fund_id,amt ,act 这些列
实际上要统计的是每个fund_id下,对应的机构下对应操作(act)的sum(amt)
靠,好拗口啊。
就
fund_id |1 |2 |3 |...
-----------------------------------------------------------------------------
|1 |2 |3 |
a | fund_id=a,org_id=1,act分别为1,2,3,时的sum(amt) |。。| 。 |
----------------------------------------------------------------------------- 大概是上表那个意思。
select fund_id,decode(org_id,1,sum(decode(act,1,amt,0),null) from table group by fund_id.
select fund_id,sum(decode(act,1,decode(s_org_id,1,amt,0),0)) sum1_1,
sum(decode(act,2,decode(s_org_id,1,amt,0),0)) sum1_2,
sum(decode(act,3,decode(s_org_id,1,amt,0),0)) sum1_3,
...
sum(decode(act,1,decode(s_org_id,15,amt,0),0)) sum15_1,
sum(decode(act,2,decode(s_org_id,15,amt,0),0)) sum15_2,
sum(decode(act,3,decode(s_org_id,15,amt,0),0)) sum15_3
from test group by fund_id.