参考如下例子:(转贴)求一查询语句,所有列数据类型为varchar
列名 col1 col2 col3
A X n
A X v
A X a
A Y t
A Y j
......查询结果
A X n,v,a
A Y t,j
......
一条SQL语句能完成吗?--一条SQL语句+一个自定义函数可以
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
--测试--测试数据
create table 表(col1 varchar(10),col2 varchar(10),col3 varchar(10))
insert 表 select 'A','X','n'
union all select 'A','X','v'
union all select 'A','X','a'
union all select 'A','Y','t'
union all select 'A','Y','j'
go--一条SQL语句+一个自定义函数可以
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
go--删除测试
drop table 表
drop function f_str/*-测试结果col1 col2 col3
---------- ---------- ------------
A X n,v,a
A Y t,j(所影响的行数为 2 行)
--*/
建表:create table tn2 (col1 varchar(10),col2 varchar(10),col3 varchar(10))输入:
insert into tn2 select
'A' ,'X' ,'n'
union select
'A' ,'X' ,'v'
union select
'A' ,'X' ,'a'
union select
'A' ,'Y' ,'t'
union select
'A' ,'Y' ,'j'
构造函数:create function f_try(@a varchar(10))
returns varchar(100)
as
begin
declare @b varchar(100)
set @b=''
select @b=@b+','+col3 from tn2 where col2= @a
set @b=reverse(right(@b,len(@b)-1))
return(@b)
end运行:
select col1,col2,dbo.f_try(col2) from tn2 group by col1,col2结果:
A X n,v,a
A Y t,j
列名 col1 col2 col3
A X n
A X v
A X a
A Y t
A Y j
......查询结果
A X n,v,a
A Y t,j
......
一条SQL语句能完成吗?--一条SQL语句+一个自定义函数可以
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
--测试--测试数据
create table 表(col1 varchar(10),col2 varchar(10),col3 varchar(10))
insert 表 select 'A','X','n'
union all select 'A','X','v'
union all select 'A','X','a'
union all select 'A','Y','t'
union all select 'A','Y','j'
go--一条SQL语句+一个自定义函数可以
create function f_str(
@col1 varchar(10),
@col2 varchar(10)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+col3
from 表
where col1=@col1 and col2=@col2
return(stuff(@r,1,1,''))
end
go--调用函数实现查询
select col1,col2,col3=dbo.f_str(col1,col2)
from 表
group by col1,col2
go--删除测试
drop table 表
drop function f_str/*-测试结果col1 col2 col3
---------- ---------- ------------
A X n,v,a
A Y t,j(所影响的行数为 2 行)
--*/
建表:create table tn2 (col1 varchar(10),col2 varchar(10),col3 varchar(10))输入:
insert into tn2 select
'A' ,'X' ,'n'
union select
'A' ,'X' ,'v'
union select
'A' ,'X' ,'a'
union select
'A' ,'Y' ,'t'
union select
'A' ,'Y' ,'j'
构造函数:create function f_try(@a varchar(10))
returns varchar(100)
as
begin
declare @b varchar(100)
set @b=''
select @b=@b+','+col3 from tn2 where col2= @a
set @b=reverse(right(@b,len(@b)-1))
return(@b)
end运行:
select col1,col2,dbo.f_try(col2) from tn2 group by col1,col2结果:
A X n,v,a
A Y t,j
update test set a=@id,b=@b+b+',',@id=a,@b=bselect a,max(b) from test
where a=@id
where a=@idselect a,max(b) from test
把这三行放到查询分析器中出错,是不是要用存储过程呀?