这个SQL语句的怎么写？顶者有分！

[m777]

A表：
Code a字段 b字段
1 0 0
1.1 0 0
1.1.2 1 1
2 0 0
2.1 0 0
2.1.1 2 2
2.1.2 2 2
2.2 0 0
2.2.1 1 1
2.2.2 0 0

------------------------
查询之后,根据上面的数据，统计字段的合计,可以用like求出合计
Code a合计 b合计
1 1 1
1.1 1 1
1.1.2 1 1
2 4 4
2.1 4 4
2.1.1 2 2
2.1.2 2 2
2.2 1 1
2.2.1 1 1
2.2.2 0 0

合计方法：
例如合计第一行CODE为1的：select Code,sum(a字段),sum(b字段) from A where Code like '1.%' Group by Code
例如合计第二行CODE为1.1的：select Code,sum(a字段),sum(b字段) from A where Code like '1.1.%' Group by Code
以次类推！

[pbsql]

select Code,
a合计=(select sum(a字段) from A where Code like x.Code+'%'),
b合计=(select sum(b字段) from A where Code like x.Code+'%')
from A x

[wangdehao]

select Code,sum(a字段),sum(b字段) from A where Code like code+'.%' Group by Code

[wangdehao]

倒,错了

[paoluo]

Select
Code,
(Select SUM(a) from A Where Code Like T.Code +'%' ) As a合计,
(Select SUM(b) from A Where Code Like T.Code +'%' ) As b合计
from A T
Group By Code

[wangdehao]

select Code,
a合计=(select sum(a字段) from A where Code like x.Code+'.%'),
b合计=(select sum(b字段) from A where Code like x.Code+'.%')
from A x

[paoluo]

晕，又慢了。
--建立测试环境
Create Table A
(Code Varchar(10),
a Int,
b int)
Insert A Values('1', 0, 0)
Insert A Values('1.1', 0, 0)
Insert A Values('1.1.2', 1, 1)
Insert A Values('2', 0, 0)
Insert A Values('2.1', 0, 0)
Insert A Values('2.1.1', 2, 2)
Insert A Values('2.1.2', 2, 2)
Insert A Values('2.2', 0, 0)
Insert A Values('2.2.1', 1, 1)
Insert A Values('2.2.2', 0, 0)
--测试
Select
Code,
(Select SUM(a) from A Where Code Like T.Code +'%' ) As a合计,
(Select SUM(b) from A Where Code Like T.Code +'%' ) As b合计
from A T
--删除测试环境
Drop Table A
--结果
/*
Code a合计 b合计
1 1 1
1.1 1 1
1.1.2 1 1
2 5 5
2.1 4 4
2.1.1 2 2
2.1.2 2 2
2.2 1 1
2.2.1 1 1
2.2.2 0 0
*/

[paoluo]

楼主的计算有点问题

2 4 4

应该为

2 5 5

[hyxgdzj]

关注中....

[子陌红尘]

--生成测试数据
create table #T(Code varchar(10),a int,b int)
insert into #T select '1' ,0,0
insert into #T select '1.1' ,0,0
insert into #T select '1.1.2',1,1
insert into #T select '2' ,0,0
insert into #T select '2.1' ,0,0
insert into #T select '2.1.1',2,2
insert into #T select '2.1.2',2,2
insert into #T select '2.2' ,0,0
insert into #T select '2.2.1',1,1
insert into #T select '2.2.2',0,0


--执行查询
select
a.Code,
a合计 = SUM(b.a),
b合计 = SUM(b.b)
from
#t a,
#t b
where
b.Code+'.' like a.Code+'.%'
group by
a.Code


--输出结果
Code a合计 b合计
------- ------ ------
1 1 1
1.1 1 1
1.1.2 1 1
2 5 5
2.1 4 4
2.1.1 2 2
2.1.2 2 2
2.2 1 1
2.2.1 1 1
2.2.2 0 0