我有一数据库,表结构如下:
create table tb(name varchar(10),t1 float,t2 float,t3 varchar(10))
insert ta select 'AB', 12.5, 1.2, 'A'
union all select 'CD', 1.1, 1.4, 'A'
union all select 'EF', 1.6, 1.7, 'A'
union all select 'GH', 1.6, 1.7, 'B'
我想得到T3=A的,第一列字符相加,2.3列数字相加,如: “AB,CD,EF” “15.2” “4.3” “A”
用一个SQL语句,比如 select ????,SUM(t1) AS Expr1, SUM(t2) AS Expr2, t3 FROM tb WHERE t3='A' GROUP BY t3
我看了别人的帖子,是用的函数,要是用函数是在那定义,是定义函数以后就能用了,还是每次都在SQL语句里写用完就没了,谢谢
create table tb(name varchar(10),t1 float,t2 float,t3 varchar(10))
insert ta select 'AB', 12.5, 1.2, 'A'
union all select 'CD', 1.1, 1.4, 'A'
union all select 'EF', 1.6, 1.7, 'A'
union all select 'GH', 1.6, 1.7, 'B'
我想得到T3=A的,第一列字符相加,2.3列数字相加,如: “AB,CD,EF” “15.2” “4.3” “A”
用一个SQL语句,比如 select ????,SUM(t1) AS Expr1, SUM(t2) AS Expr2, t3 FROM tb WHERE t3='A' GROUP BY t3
我看了别人的帖子,是用的函数,要是用函数是在那定义,是定义函数以后就能用了,还是每次都在SQL语句里写用完就没了,谢谢
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/864b393f-225f-4895-8c8d-4db59ea60032.htm
CREATE function test(@t3 varchar(10))
returns varchar(30)
as
begin
declare @t1 varchar(10)
declare @result varchar(30)
SET @result=''
declare aa cursor for select name from tb where t3=@t3
open aa
fetch next from aa into @t1
while @@fetch_status=0
begin
set @result=@result+@t1
fetch next from aa into @t1
end
close aa
deallocate aa
return @result
endselect dbo.test(t3) as name,sum(t1),sum(t2) from tb group by t3