1.4w+
社区成员
25.3w+
社区内容
- 主页
- 基础类
- 应用实例
- 新技术前沿
SQL语句如何写？
假设表结构为id，amount，remark。
remark为备注。如何实现下列的效果
select id,sum(amount) as sum,remark叠加 from tablename
group by id order by id。
其中remark叠加 要求相同id的remark以某种格式连接起来。
remark为备注。如何实现下列的效果
select id,sum(amount) as sum,remark叠加 from tablename
group by id order by id。
其中remark叠加 要求相同id的remark以某种格式连接起来。
...全文
jinjazz 2005年03月23日
--建立测试环境
Create Table 表(id varchar(10),amount integer,remark 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+','+remark FROM 表 WHERE id=@vchA
RETURN(substring(@r,2,8000))
END
GO
select id,sum(amount) as sum,dbo.FunMergeCharField(id) as remark叠加 from 表 group by id
--删除测试环境
Drop Table 表
Drop FUNCTION FunMergeCharField
Create Table 表(id varchar(10),amount integer,remark 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+','+remark FROM 表 WHERE id=@vchA
RETURN(substring(@r,2,8000))
END
GO
select id,sum(amount) as sum,dbo.FunMergeCharField(id) as remark叠加 from 表 group by id
--删除测试环境
Drop Table 表
Drop FUNCTION FunMergeCharField
xluzhong 2005年03月23日
create function f_getrm(@id int)
returns nvarchar(1000)
as
declare @s nvarchar(1000)
set @s=''
select @s=@s+','+remark from tablename where id=@id
return(stuff(@s,1,1,''))
go
select id,
sum(amount) as sum,
dbo.getrm(id) as remark
group by id
order by id
returns nvarchar(1000)
as
declare @s nvarchar(1000)
set @s=''
select @s=@s+','+remark from tablename where id=@id
return(stuff(@s,1,1,''))
go
select id,
sum(amount) as sum,
dbo.getrm(id) as remark
group by id
order by id