求助於有點難度的SQL語句 ( 积分: 50 )

我現在要實現如下功能，望各高手前來幫助!如：有以下數據：
字段1 字段2 字段3
A, B, C1,
A, B, C2
A1, B2, D1
A1, B2, D2
A2, B3, E1
A2, B3, E2
現在我要得到以下數據，請問如何用SQL語句實現？
A,B,C1+C2
A1,B2,D1+D2
A2,B3,E1+E2

select 字段1,字段2,sum(字段3)
from 表 group by 字段1,字段2

需要编程解决,假设你的表为id,字段1,字段2,字段3
第一步,select 字段1,字段2 group by 字段1,字段2
第二步,通过循环逐条获取数据,可以把结果给一个临时表
循环处理细节就很灵活相信你自己完全可以解决

select 字段1,字段2,sum(字段3)
from 表 group by 字段1,字段2

就可以了

不是的啊~!不能用SUM语句啊!因为那个字段不是数字类型的而是字符类型的啊!我要把那个字段的字符加起来!

create table Tmptable
(
a varchar(10),
b varchar(10),
c Varchar(10)
)
declare win cursor
for select a,b from Tab group by a,b
open win
declare @a VarChar(10),@b VarChar(10)
fetch next from win into @a,@b
while @@fetch_status=0
begin
declare winone cursor
for select c from Tab where a=@a and b=@b
open winone
declare @c Varchar(10),@s Varchar(10)
set @s=''
fetch next from winone into @c
while @@fetch_status=0
begin
set @s=@s+@c
fetch next from winone into @c
end
insert into Tmptable(a,b,c)
values(@a,@b,@s)
deallocate winOne
fetch next from win into @a,@b
end;
deallocate win
select * from TmpTable
delete TmpTable

DECLARE @SQLA VARCHAR(6000),@SQLB VARCHAR(8000),@SQLC VARCHAR(8000)
IF object_id('tempdb..##tableA')IS NOT NULL DROP TABLE ##tableA
IF object_id('tempdb..##tableB')IS NOT NULL DROP TABLE ##tableB
set @SQLA=' SELECT * INTO ##tableA FROM
(SELECT Number,Lasts,Heel,SuggestedPrice,ColorsAvailable FROM TSHOE) A ORDER BY Lasts '
EXEC(@SQLA)
Create Table [dbo].[##tableB]
(
T_Number varchar(50 ) Null ,
T_Lasts varchar(50) null ,
T_Price decimal(10,2) null ,
T_Heel varchar(50) null ,
T_Color_A varchar(5000))
Declare @v_Num varchar(50),@v_Lasts varchar(50),@v_price decimal(10,2),@v_Heel varchar(50),
@v_Color_A varchar(5000),@v_s varchar(5000)

Declare win Cursor
FOR
SELECT Number,Lasts,Heel,SuggestedPrice FROM ##tableA Group by Number,Lasts,Heel,SuggestedPrice
Open win

Fetch Next From win into @v_Num,@v_Lasts,@v_Heel,@v_price
While @@Fetch_Status=0
begin
declare winone cursor
for
select ColorsAvailable from ##tableA
where Number=@v_Num and Lasts=@v_Lasts and Heel=@v_heel and SuggestedPrice=@v_price
open winone
set @v_s=''
fetch next from winone into @v_color_A
while @@fetch_status=10
begin
set @v_s=@v_s+@v_color_A
fetch next from winone into @v_color_A
end
insert into ##tableB
(T_Number,T_Lasts,T_Price,T_Heel,T_Color_A)
values(@v_Num,@v_Lasts,@v_price,@v_Heel,@v_s)
deallocate winone
fetch next from win into @v_Num,@v_Lasts,@v_Heel,@v_price
end
deallocate win
select * from ##tableB
帮我修改一下好吗？@v_s中没有值!

select 字段1,字段2,sum(字段3)
from 表 group by 字段1,字段2
===========================================
如果"字段3"真的是数值类型就可以了！ 但如果是字符的话有点麻烦。

使用自定函数
create function JoinStr2(@f1 varchar(12),@f2 varchar(4))
returns varchar(2000)
as
begin
declare @s as varchar(2000)
select @s=''
select @s=@s + rtrim(f3) +'+' from tmp
where f1=@f1 and f2=@f2
select @s=Left(@s,len(@s)-1)
return @s
end

// 测试
create table tmp
(f1 varchar(5),f2 varchar(6),f3 varchar(6))
insert tmp
select 'A','B','C1'
union select 'A','B','C2'
union select 'A1','B','C3'
union select 'A1','B1','C4'
union select 'A1','B1','C5'
union select 'A1','B1','C6'
select * from tmp
select f1,f2,dbo.JoinStr2(f1,f2) as f_3 from tmp group by f1,f2
// 结果
A B C1+C2
A1 B C3
A1 B1 C4+C5+C6

CREATE function sumstr(@f1 varchar(12),@f2 varchar(4),@f3 varchar(50),@f4 decimal(10,2))
returns varchar(8000)
as
begin
declare @s as varchar(8000)
select @s=''
select @s=@s +isnull( rtrim(colorsavailable),' ') +',' from tshoe
where isnull (number,'')=isnull(@f1,'') and isnull(lasts,'')=isnull(@f2,'') and isnull(heel,'')=isnull(@f3,'') and isnull(suggestedprice,0)=isnull(@f4,0)
--select @s=Left(@s,len(@s)-1)
return @s
end


select number,lasts,heel,suggestedprice,FilePath,LastNumber,Lasts,Const,
dbo.sumstr(number,lasts,heel,suggestedprice) ColorsAvailable '
from tshoe
為什麼有的字段統計出來為空的？要加總統計的colorsavailable字段數據庫中沒有為空的值!

我找到答案了，是函数中的变量长度小于字段的长度，我改了以后就可以了!现在散分

多人接受答案了。