满分求一SQL语句或存储过程或算法

[wrxian]

表Table1：
项目 金额
X1 100
X2 120
Y1 Y3 180
Y2 90
Z1 140
A1 A2 B1 300
B2 Z2 C1 150
要得到以下结果：
项目 金额
A 200
B 150
C 50
X 220
Y 270
Z 190
注：即头字母相同的累加，一个项目中如果有多于一个值则等分（如A1 A2 B1：300则A 200,B 100）。

[zjcxc]

--处理示例

create table Table1(项目 varchar(10),金额 int)
insert Table1 select 'X1' ,100
union all select 'X2' ,120
union all select 'Y1 Y3' ,180
union all select 'Y2' ,90
union all select 'Z1' ,140
union all select 'A1 A2 B1',300
union all select 'B2 Z2 C1',150
go

--查询处理

--处理临时表
declare @i int
select @i=max(len(项目)) from Table1
set rowcount @i
select id=identity(int) into #t from syscolumns a,syscolumns b
set rowcount 0

--统计出结果
select 项目=substring(a.项目,b.id,1)
,金额=sum(a.金额/(len(a.项目)-len(replace(a.项目,' ',''))+1))
from Table1 a,#t b
where len(a.项目)>=b.id and charindex(' ',' '+a.项目,b.id)=b.id
group by substring(a.项目,b.id,1)
drop table #t
go

--删除测试
drop table Table1

/*--测试结果

项目 金额
---- -----------
A 200
B 150
C 50
X 220
Y 270
Z 190

（所影响的行数为 6 行）
--*/

[lsxaa]

select a.col1,sum(b.金额/(len(b.项目)-len(replace(b.项目,' ',''))+1)) as 金额
from (select 'A' as col1 union all
'B' as col1 union all
...
'Z' as col1 ) a,表 b
where a.col1=charindex(a.col1,b.项目)>0
group by a.col1 c

[lsxaa]

select a.col1,sum(b.金额/(len(b.项目)-len(replace(b.项目,' ',''))+1)) as 金额
from (select 'A' as col1 union all
'B' as col1 union all
...
'Z' as col1 ) a,表 b
where a.col1=charindex(a.col1,b.项目)>0
group by a.col1

[lsxaa]

上面两个有问题，这个可以 欢迎大家提意见
select a.col1,sum((b.金额/(len(b.项目)-len(replace(b.项目,' ',''))+1))
*(len(a.col1)-len(replace(a.col1,' ','')))
) as 金额


from (select 'A' as col1 union all
'B' as col1 union all
...
'Z' as col1 ) a,表 b
where a.col1=charindex(a.col1,b.项目)>0
group by a.col1

[lsxaa]

怎么又错，最后修正

select a.col1,sum( (b.金额/(len(b.项目)-len(replace(b.项目,' ',''))+1))
*(len(b.项目)-len(replace(b.项目,a.col1,'')))
) as 金额
from (select 'A' as col1 union all select
'B' as col1 union all select
'C' as col1 union all select
......
'X' as col1 ) a,表 b
where charindex(a.col1,b.项目)>0
group by a.col1

[playyuer]

此类问题都是先把字符串立起来!

[dragonlyf]

学习～

[lsxaa]

楼主的表设计有问题，应该有一个项目编号表

[wrxian]

问题基本解决，可是项目中如果两个值之间由于录入不规范空格不止一个字母办？

[淡蓝冰]

錄入時判斷呀

[wrxian]

lsxaa(小李铅笔刀) ：
如果不能确定头字母是哪些，或不只是一个字母，怎么办？
zjcxc(邹建) 的方法不错，空格多了怎么办？

[zjcxc]

头字母应该不是问题,字母A~Z,最多26个,可以写出来,除非楼主指的头字母不只字母

空格多了的问题在他的处理方法中一样有

[zjcxc]

当然,他的处理还存在这样一个问题,如果项目值是这样的: AA1 A2 A3 B1
则统计结果会错.

[zjcxc]

--如果项目中有多余的空格,则这样:

create table Table1(项目 varchar(20),金额 int)
insert Table1 select 'X1' ,100
union all select 'X2' ,120
union all select 'Y1 Y3' ,180
union all select 'Y2' ,90
union all select 'Z1' ,140
union all select 'A1 A2 B1',300
union all select 'B2 Z2 C1' ,150
go

--查询处理

--处理临时表
declare @i int
select @i=max(len(项目)) from Table1
set rowcount @i
select id=identity(int) into #t from syscolumns a,syscolumns b
set rowcount 0

--统计出结果
select 项目,项目1=substring(a.项目,b.id,1),金额
into #t1 from Table1 a,#t b
where len(a.项目)>=b.id and patindex(' [^ ]%',substring(' '+a.项目,b.id,8000))=1

select 项目=a.项目1,金额=sum(a.金额/b.cnt)
from #t1 a,(select 项目,cnt=count(*) from #t1 group by 项目)b
where a.项目=b.项目
group by a.项目1

drop table #t,#t1
go

--删除测试
drop table Table1

/*--测试结果

项目 金额
---- -----------
A 200
B 150
C 50
X 220
Y 270
Z 190

（所影响的行数为 6 行）
--*/