--建立测试环境
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
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
解决方案 »
- 请问一个复杂的分页sql语句
- select * from userinfo where dateadd(day,1,shengri)='1981-4-24 00:00:00.00' dateadd函数返回一个datetime类型
- 请教一个简单问题
- 请教两个数据库查询的问题,分不够可再开贴,顶者有分!~~
- sql按查询条件过滤出数据导到EXECL出错,请高人看看是什么,怎么改正!
- 创建的视图如何添加第一项是自动编号?
- 装完sql2005后没实例
- 有两个字段唯一确定表里是否有那些记录,如果没有就插入这些记录,要怎样做???
- 很简单的问题,多表查询
- 多用户同时更新数据解决数据完整性问题,谢谢?
- 关于导入EXCEL的问题,请大家帮忙~~
- 急问数据库更新问题?
insert @ta select
1 ,'aa' union select
2 ,'bb' union select
3 ,'cc' union select
5 ,'dd'
declare @tb table(id int,allname varchar(100))
insert @tb select 1,nulldeclare @s varchar(100)
select @s =isnull(@s+',','')+ltrim(id) from @ta
update @tb
set allname = @s
where id = 1
select * from @tb/*
id allname
----------- ----------------------------------------------------------------------------------------------------
1 1,2,3,5(所影响的行数为 1 行)
*/
这个是2005的用一条语句更新create table #tba (id int,name varchar(10))
insert into #tba values(1,'aa')
insert into #tba values(2,'bb')
insert into #tba values(3,'cc')
insert into #tba values(4,'dd')
create table #tbb (id int,allname varchar(100))
insert into #tbb values(1,null)update #tbb set allname=a.id from
(
select id=cast(id as varchar(100)) from
(
select id as [text()] from #tba for xml path(''),type
) x(id)
)aselect * from #tbbid allname
----------- ----------------------------------------------------------------------------------------------------
1 1234(1 行受影响)
(
id int,
name char(2)
)
insert into tb
select
1,'aa' union
select
2,'bb' union
select
3,'cc' union
select
5,'dd'
create table tb1
(
id int,
name varchar(800)
)
insert into tb1(id) values (1)
--把一列变一行
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + convert(varchar,id) from tb
print @output
update tb1 set name = @output where id = 1
select * from tb1
id name1 1,2,3,5(1 row(s) affected)
create table #tba (id int,name varchar(10))
insert into #tba values(1,'aa')
insert into #tba values(2,'bb')
insert into #tba values(3,'cc')
insert into #tba values(4,'dd')
create table #tbb (id int,allname varchar(100))
insert into #tbb values(1,null)
update #tbb set allname=stuff(a.id,1,1,'') from
(
select id=cast(id as varchar(100)) from
(
select ','+cast(id as varchar(10)) as [text()] from #tba for xml path(''),type
) x(id)
)a
select * from #tbbid allname
----------- ----------------------------------------------------------------------------------------------------
1 1,2,3,4(1 行受影响)