！！！！！！求一sql语句！！！！！！！！(50分)

F

fhuibo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-08-20

• #1

表 A 有两个字段 &nbsp; (其中ff字段是2-20位不等的字符串，没有规律)<br>ff(字符) &nbsp;cc(int)<br>01 &nbsp; &nbsp; &nbsp; &nbsp; 3<br>01 &nbsp; &nbsp; &nbsp; &nbsp; 2<br>0102 &nbsp; &nbsp; &nbsp; 4<br>010201 &nbsp; &nbsp; 8<br>0103 &nbsp; &nbsp; &nbsp; 5<br>02 &nbsp; &nbsp; &nbsp; &nbsp; 2<br>0201 &nbsp; &nbsp; &nbsp; 3<br>0201 &nbsp; &nbsp; &nbsp; 4<br>我想按ff分组求 cc的合计 但要包含下级的代码，比如 01 要把以 01 开头的都 sum 起来<br>0102 要把以 0102 开头的sum 起来，02 的要把以 02 开头的sum起来。。。。。。<br>要求结果：<br>ff &nbsp; &nbsp;cc<br>01 &nbsp; &nbsp;22<br>0102 &nbsp;12<br>010201 8<br>0103 &nbsp; 5<br>02 &nbsp; &nbsp; 9<br>0201 &nbsp; 7

 

C

crazyD

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-08-20

• #2

select ff,sum(cc) as cc from 表　 group by &nbsp;left(ff,2)<br>union all<br>select ff,sum(cc) as cc from 表　group by &nbsp;left(ff,4)<br>union all<br>select ff,sum(cc) as cc from 表　group by &nbsp;left(ff,6)<br>union all<br>select ff,sum(cc) as cc from 表　group by &nbsp;left(ff,8)

 

F

fhuibo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-08-20

• #3

楼上大哥，忘了告诉你，我的ff字段 是没有规律的，并且还是变化的。也就是说2-20位是不等的，再说了你的sql语句能执行吗？

 

T

tandxu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-08-20

• #4

规律是不是len(ff)&lt;len(ff1) and ff1 like 'ff%'?<br>select ff,Sum(cc) from<br>(<br>select tb.ff,ttt.* from tb left join (select * from tb) ttt on len(tb.ff) &lt;= len(ttt.ff) and ttt.ff like tb.ff+'%' <br>) ttttt<br>Group By ff

 

O

onyliu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-08-20

• #5

如果数据量小的话 用一个SQL解决是可行的 虽然麻烦点 分别汇总2－20位 （19个语句联合下）<br>奉劝你另外做一张汇总表，逐条记录累加即可

 

M

milygo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-08-20

• #6

select ff, sum(cc) from TABLE group by ff order by ff; 即可 因为ff是字符型，排序的话就是按照你要求的排的。oracle下测试通过。

 

C

crazyD

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-08-20

• #7

我的ＳＱＬ　不能执行？　　如果是ＳＱＬＳＥＲＶＥＲ是问题的．<br><br>而且这只是是思路而已，　不会有现成的东西　　拿过来复制粘贴就可以用的，　另外我觉得　　表结构还是可以再修改的，　　应该有一个字段来描述ff的层次　，　这样会好些．　　静待高手提供的ＳＱＬ．

 

D

david_wu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-08-25

• #8

SELECT ff,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (SELECT SUM(cc)<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM TABLE1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE ff LIKE a.ff + '%') AS cc<br>FROM TABLE1 a<br>GROUP BY ff

 

Q

qq112729650

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-08-25

• #9

group by ff having(Count(*))&gt;0

 

Q

qq112729650

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-08-25

• #10

select ff,sum(cc )from A<br>group by ff

 

E

eyusir

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-08-26

• #11

SELECT ff,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (SELECT SUM(cc)<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM TABLE1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE ff LIKE a.ff + '%') AS cc<br>FROM TABLE1 a<br>GROUP BY ff

 

F

fhuibo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-08-26

• #12

多人接受答案了。