--写个处理的函数 create function f_str( @WZOID int )returns varchar(8000) as begin declare @r varchar(8000) set @r='' select @r=@r+':'+rtrim(XQDW) from 表 where WZOID=@WZOID return(stuff(@r,1,1,'')) end go--调用实现查询 select OID=min(OID),WZOID,XQDW=dbo.f_str(WZOID),XQSL=sum(XQSL) from 表 group by WZOID
写一函数,比如名为;getXQDW, WZOID作为参数,返回一字符串 比如: A:10,C:3 调用 select min(OID),WZOID, XQDW =dbo.getXQDW(WZOID), XQSL=sum(XQSL) FROM 表 GROUP BY WZOID
改了一下 alter function f_str( @WZOID int )returns varchar(8000) as begin declare @r varchar(8000) set @r='' select @r=@r+':'+rtrim(XQDW)+':'+cast(xqsl as varchar(20)) from 表 where WZOID=@WZOID return(stuff(@r,1,1,'')) end go--调用实现查询 select OID=min(OID),WZOID,XQDW=dbo.f_str(WZOID),XQSL=sum(XQSL) from 表 group by WZOID
完整的 create table test1 (oid int,wzoid int,xqdw varchar(10),xqsl int) go insert test1 select 1,100,'a',10 union all select 2,101,'b',5 union all select 3,100,'c',3 union all select 4,102,'d',2 union all select 5,101,'e',3 gocreate function f_str( @WZOID int )returns varchar(8000) as begin declare @r varchar(8000) set @r='' select @r=@r+':'+rtrim(XQDW)+':'+cast(xqsl as varchar(20)) from test1 where WZOID=@WZOID return(stuff(@r,1,1,'')) end go--调用实现查询 select OID=min(OID),WZOID,XQDW=dbo.f_str(WZOID),XQSL=sum(XQSL) from test1 group by WZOIDdrop function f_str drop table test1
--没有把XQSL合并上去--测试--测试数据 create table 表(OID int,WZOID int,XQDW varchar(10),XQSL int) insert 表 select 1,100,'A',10 union all select 2,101,'B',5 union all select 3,100,'C',3 union all select 4,102,'D',2 union all select 5,101,'E',3 go--处理的函数 create function f_str( @WZOID int )returns varchar(8000) as begin declare @r varchar(8000) set @r='' select @r=@r+','+rtrim(XQDW)+':'+rtrim(XQSL) from 表 where WZOID=@WZOID return(stuff(@r,1,1,'')) end go--调用实现查询 select OID=min(OID),WZOID,XQDW=dbo.f_str(WZOID),XQSL=sum(XQSL) from 表 group by WZOID go--删除测试 drop table 表 drop function f_str/*--测试结果OID WZOID XQDW XQSL ----------- ----------- ----------- ------ 1 100 A:10,C:3 13 2 101 B:5,E:3 8 4 102 D:2 2(所影响的行数为 3 行) --*/
create function f_str(
@WZOID int
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+':'+rtrim(XQDW)
from 表 where WZOID=@WZOID
return(stuff(@r,1,1,''))
end
go--调用实现查询
select OID=min(OID),WZOID,XQDW=dbo.f_str(WZOID),XQSL=sum(XQSL)
from 表
group by WZOID
select min(OID),WZOID, XQDW =dbo.getXQDW(WZOID), XQSL=sum(XQSL)
FROM 表
GROUP BY WZOID
alter function f_str(
@WZOID int
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+':'+rtrim(XQDW)+':'+cast(xqsl as varchar(20))
from 表 where WZOID=@WZOID
return(stuff(@r,1,1,''))
end
go--调用实现查询
select OID=min(OID),WZOID,XQDW=dbo.f_str(WZOID),XQSL=sum(XQSL)
from 表
group by WZOID
create table test1
(oid int,wzoid int,xqdw varchar(10),xqsl int)
go
insert test1
select 1,100,'a',10
union all
select 2,101,'b',5
union all
select 3,100,'c',3
union all
select 4,102,'d',2
union all
select 5,101,'e',3
gocreate function f_str(
@WZOID int
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+':'+rtrim(XQDW)+':'+cast(xqsl as varchar(20))
from test1 where WZOID=@WZOID
return(stuff(@r,1,1,''))
end
go--调用实现查询
select OID=min(OID),WZOID,XQDW=dbo.f_str(WZOID),XQSL=sum(XQSL)
from test1
group by WZOIDdrop function f_str
drop table test1
create table 表(OID int,WZOID int,XQDW varchar(10),XQSL int)
insert 表 select 1,100,'A',10
union all select 2,101,'B',5
union all select 3,100,'C',3
union all select 4,102,'D',2
union all select 5,101,'E',3
go--处理的函数
create function f_str(
@WZOID int
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+rtrim(XQDW)+':'+rtrim(XQSL)
from 表 where WZOID=@WZOID
return(stuff(@r,1,1,''))
end
go--调用实现查询
select OID=min(OID),WZOID,XQDW=dbo.f_str(WZOID),XQSL=sum(XQSL)
from 表
group by WZOID
go--删除测试
drop table 表
drop function f_str/*--测试结果OID WZOID XQDW XQSL
----------- ----------- ----------- ------
1 100 A:10,C:3 13
2 101 B:5,E:3 8
4 102 D:2 2(所影响的行数为 3 行)
--*/