if exists(select name from sysobjects where name='makeskb')---這裡加多一個條件)否則可能刪除有用的東西) if exists(select name from sysobjects where name='makeskb'and xtype='p')--
--建立测试环境 Create Table 表(id varchar(10),amount integer,re varchar(10)) --插入数据 insert into 表 select '1','3','aaa' union select '1','5','bbb' union select '1','4','ccc' union select '2','10','pkoge' union select '2','12','daf' go --测试语句 CREATE FUNCTION FunMergeCharField(@vchA varchar(10)) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r='' SELECT @r=@r+','+re FROM 表 WHERE id=@vchA RETURN(substring(@r,2,8000)) END GO select id,sum(amount) as sum,dbo.FunMergeCharField(id) as re叠加 from 表 group by id
--删除测试环境 Drop Table 表 Drop FUNCTION FunMergeCharField/* id sum re叠加 ---------- ----------- --------------- 1 12 aaa,ccc,bbb 2 22 pkoge,daf */
--先写个自定义函数 create function dbo.f_linkbb (@a char(1)) returns varchar(100) as begin declare @s varchar(100) set @s='' select @s=@s+B+',' from 表 where A=@a select @s=left(@s,len(@s)-1) return(@s) end --查询select A,dbo.f_linkbb(A),sum(C) from 表 group by A,dbo.f_linkbb(A)
if exists(select name from sysobjects where name='makeskb'and xtype='p')--
Create Table 表(id varchar(10),amount integer,re varchar(10))
--插入数据
insert into 表
select '1','3','aaa' union
select '1','5','bbb' union
select '1','4','ccc' union
select '2','10','pkoge' union
select '2','12','daf'
go
--测试语句
CREATE FUNCTION FunMergeCharField(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+re FROM 表 WHERE id=@vchA
RETURN(substring(@r,2,8000))
END
GO
select id,sum(amount) as sum,dbo.FunMergeCharField(id) as re叠加 from 表 group by id
--删除测试环境
Drop Table 表
Drop FUNCTION FunMergeCharField/*
id sum re叠加
---------- ----------- ---------------
1 12 aaa,ccc,bbb
2 22 pkoge,daf
*/
create function dbo.f_linkbb
(@a char(1))
returns varchar(100)
as
begin
declare @s varchar(100)
set @s=''
select @s=@s+B+',' from 表 where A=@a
select @s=left(@s,len(@s)-1)
return(@s)
end
--查询select A,dbo.f_linkbb(A),sum(C) from 表
group by A,dbo.f_linkbb(A)