例如有表:表A
===========================
id name age
1 jason 29
2 ken 33
3 yuan 26
...等等...........我想用一条查询语句,不使用变量,只使用一条select 语句,能否得到以下结果:jason,ken,yuan....意思就是想把name字段的内容串起来
===========================
id name age
1 jason 29
2 ken 33
3 yuan 26
...等等...........我想用一条查询语句,不使用变量,只使用一条select 语句,能否得到以下结果:jason,ken,yuan....意思就是想把name字段的内容串起来
DECLARE @STR VARCHAR(8000)SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT NAME FROM tb)AS TSELECT @STR
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-02 11:03:39
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([id] int,[name] varchar(5),[age] int)
insert [a]
select 1,'jason',29 union all
select 2,'ken',33 union all
select 3,'yuan',26
--------------开始查询--------------------------DECLARE @STR VARCHAR(8000)SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT NAME FROM A)AS TSELECT @STR
----------------结果----------------------------
/* ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
jason,ken,yuan(1 行受影响)
*/
只用select,而且是一条过
create function dbo.add_str(@a varchar(20))
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+name+',' from table1 where name=@a
return (left(@s,len(@s)-1))
end
select id,sum3=dbo.add_str(name) from table1 group by name
-- SQL Server 2005
select stuff((select ','+name from tab for xml path('')),1,1,'')
这里有个笨办法:select t1.name + ',' + t2.name + ',' + t3.name +...+ tn.name
from a AS t1, a AS t2, a AS t3,...,a AS tn --设有表A有n行
where t1.id = 1 and
t2.id = 2 and
t3.id = 3 and
...
tn.id = n;--借用一下2楼的部分代码,懒得输入--if object_id('[a]') is not null drop table [a]
--go
--create table [a]([id] int,[name] varchar(5),[age] int)
--insert [a]
--select 1,'jason',29 union all
--select 2,'ken',33 union all
--select 3,'yuan',26
--select * from a
--查询select t1.name + ',' + t2.name + ',' + t3.name as [all in one]
from a AS t1, a AS t2, a AS t3--设有表A有n行
where t1.id = 1 and
t2.id = 2 and
t3.id = 3/*
结果:
all in one
-----------------
jason,ken,yuan(1 行受影响)*/
SELECT * FROM(SELECT DISTINCT id FROM 表)A OUTER APPLY(
SELECT [values]=stuff(replace(replace((
SELECT name FROM 表 N
FOR XML AUTO ),'<N value="',','),'"/>',''),1,1,'')
)N
if exists (select * from dbo.sysobjects where name = 'tb' and type = 'U') drop table [dbo].tb
create table tb (UserID int , UserName nvarchar(10),CityName nvarchar(10));
insert into tb (UserID,UserName,CityName) values (1,'a','上海')
insert into tb (UserID,UserName,CityName) values (2,'b','北京')
insert into tb (UserID,UserName,CityName) values (3,'c','上海')
insert into tb (UserID,UserName,CityName) values (4,'d','北京')
insert into tb (UserID,UserName,CityName) values (5,'e','上海')select * from tb SELECT B.CityName,LEFT(UserList,LEN(UserList)-1)
FROM (SELECT CityName,(SELECT UserName+',' FROM tb WHERE CityName=A.CityName FOR XML PATH('')) AS UserList
FROM tb A
GROUP BY CityName) B
/*
CityName UserList
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
上海 a,c,e
北京 b,d(2 row(s) affected)*/
select left((select ''''+name+''',' from a for xml path('')),
len((select ''''+name+''',' from a for xml path('')))-1)