重新定义表结构,会大大加快你的查询速度最好把id 定义为末级字段(即 001-00-001)这样你在统计二级可以表示为:
select substr(id,1,6) as id ,sum(balance) as balance from xxx group by substr(id,1,6)
union
select substr(id,1,3) as id ,sum(balance) as balance from xxx group by substr(id,1,3)
select substr(id,1,6) as id ,sum(balance) as balance from xxx group by substr(id,1,6)
union
select substr(id,1,3) as id ,sum(balance) as balance from xxx group by substr(id,1,3)
from test a,test b
where instr(a.id,b.id)>0
group by b.id
SELECT SUM((CASE WHEN coid = '001-00-01' THEN coid ELSE 0 END)) as class2, (SUM((CASE WHEN coid = '001-00-01' THEN coid ELSE 0 END))+SUM((CASE WHEN coid = '001-00' THEN coid ELSE 0 END))) as class3, (SUM((CASE WHEN coid = '001-00-01' THEN coid ELSE 0 END))+SUM((CASE WHEN coid = '001-00' THEN coid ELSE 0 END))+ SUM((CASE WHEN coid = '001-01 ' THEN ELSE 0 END))) as class4 ... as class5...
第二coid的形式没有规律,只是在形式上下一级字符串包含上一级字符串,每一级安“-”区分
第三要求很简单,就是根据下一级算上一级,一级一级的算上去,还有就是要求在数据库中实现,
select b.id ,sum(a.balance)
from test a,test b
where instr(a.id,b.id)=1
group by b.id
17:16:05 jlanzpa817>select b.coid ,sum(a.balance)
17:16:22 2 from cc a,cc b
17:16:22 3 where instr(a.coid,b.coid)=1
17:16:22 4 group by b.coid
17:16:22 5 ;COID SUM(A.BALANCE)
-------------------- --------------
001 50
001-00 28
001-00-01 13
001-01 12
002 35
002-0 15
002-0-001 7
002-0-02 3已选择8行。已用时间: 00: 00: 00.00
--不只是层次关系