现在的查询结果如下:
col1 col2 col3
A B abc
A B efg
A B hig
C D abc
C D xyz
我想得到的结果是
col1 col2 col3
A B abc,efg,hig
C D abc,xyz请问下大家,应该用哪个SQL关键字处理下?谢谢!
col1 col2 col3
A B abc
A B efg
A B hig
C D abc
C D xyz
我想得到的结果是
col1 col2 col3
A B abc,efg,hig
C D abc,xyz请问下大家,应该用哪个SQL关键字处理下?谢谢!
解决方案 »
- 连接。net时报错误: 监听进程不能解析在连接描叙中给出的service_name
- 查询结果数据表 添加返回条数 急 高手出招!!!!
- 数据导入:请问如何将TXT文件的每行内容顺序的导入到表里?(这个有人问过不过没人给出答案)
- 提取不重复的数据,要怎么写
- 求SQL查询语句,在线等......
- 取相似字符串中的最小(短)值! 有哪几种写法....?
- 这蛋疼的问题,可以说是一种无中生有的技术=============纠结当疼中
- 数据库恢复问题!!
- where 条件句中的一个是另一个查询语句查询结果,怎么写?
- 关于sql server7.0数据库中image类型字段问题!
- 往表里插入数据时触发器出了问题
- SQL查询顺序
原著:邹建
改编:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳表结构,数据如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加)1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--1. 创建处理函数
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goCREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO-- 调用函数
SELECt id, value = dbo.f_str(id) FROM tb GROUP BY iddrop table tb
drop function dbo.f_str/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/--2、另外一种函数.
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct id ,dbo.f_hb(id) as value from tbdrop table tb
drop function dbo.f_hb/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb/*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc(2 行受影响)
*/--SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goselect id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc(2 row(s) affected)*/drop table tb
--2000中使用如下的函数,2005的方法见上.
create table tb(fCode varchar(10) , fName varchar(10) ,fQty int,fRe varchar(10))
insert into tb values('001' , '红色' , 500 , '大码')
insert into tb values('001' , '红色' , 300 , '小码')
go
--创建一个合并的函数
create function f_hb(@fCode varchar(10),@fName varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '+' + cast(fRe as varchar) from tb where fCode=@fCode and fName = @fName
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct fCode ,fName , sum(fQty) fQty, dbo.f_hb(fCode ,fName) as fRee from tb group by fCode ,fNamedrop table tb
drop function dbo.f_hb/*
fCode fName fQty fRee
---------- ---------- ----------- ----------
001 红色 800 大码+小码(所影响的行数为 1 行)
对于同一个col3,它前面的两个列col1和col2的值是一定的。
可能会用到
charindex
patindex
等
create table tb(col1 varchar(10),col2 varchar(10),col3 varchar(10))
insert into tb
select 'A','B', 'abc' union all
select 'A','B', 'efg' union all
select 'A','B', 'hig' union all
select 'C','D', 'abc' union all
select 'C','D', 'xyz'
go--创建一个合并的函数
create function f_hb(@col1 varchar(10),@col2 varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str + ',' + cast(col3 as varchar) from tb where col1 = @col1 and col2=@col2
set @str=right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct col1,col2,dbo.f_hb(col1,col2) as col3 from tb
godrop table tb
drop function dbo.f_hb
/*
(所影响的行数为 5 行)col1 col2 col3
---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A B abc,efg,hig
C D abc,xyz(所影响的行数为 2 行)
*/
insert into @tb select 'A','B','abc'
union all select 'A','B','efg'
union all select 'A','B','hig'
union all select 'C','D','abc'
union all select 'C','D','xyz'
select col1,col2 ,col3 = stuff((select ','+ col3 from @tb where col1=a.col1 for xml path('')),1,1,'')
from @tb a group by col1,col2
col1 col2 col3
---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A B abc,efg,hig
C D abc,xyz(2 行受影响)
insert into @tb select 'A','B','abc'
union all select 'A','B','efg'
union all select 'A','B','hig'
union all select 'C','D','abc'
union all select 'C','D','xyz'
SELECT * FROM(SELECT DISTINCT col1,col2 FROM @tb)A OUTER APPLY(
SELECT [col3]= STUFF(REPLACE(REPLACE(
(
SELECT col3 FROM @tb N
WHERE col1 = A.col1 and col2=A.col2
FOR XML AUTO
), '<N col3="', ','), '"/>', ''), 1, 1, '')
)N
/*col1 col2 col3
---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A B abc,efg,hig
C D abc,xyz(2 行受影响)*/
insert into tb
select 'A','B', 'abc' union all
select 'A','B', 'efg' union all
select 'A','B', 'hig' union all
select 'C','D', 'abc' union all
select 'C','D', 'xyz'
select * into # from tb
declare @col1 varchar(100),@sql varchar(100)
set @col1=''
set @sql=''
update # set @sql=case when @col1=col1 then @sql+','+col3 else col3 end,@col1=col1,col3=@sql
select col1,col2,max(col3) from # group by col1,col2
go
drop table tb
col1 col2
---------- ---------- ----------------------------------------------------------------------------------------------------
A B abc,efg,hig
C D abc,xyz(所影响的行数为 2 行)