--------------------------------- -- Author: htl258(Tony) -- Date : 2009-07-07 10:53:16 --------------------------------- --> 生成测试数据表-tbif not object_id('tb') is null drop table tb Go Create table tb([字段] nvarchar(11)) Insert tb select 'qwer,xxx,dd' union all select 'qwer,erete' union all select 'erete,dd' union all select 'qwer,dd' Go --Select * from tb-->SQL查询如下: with t as ( select distinct [字段]=substring(a.[字段], b.number, charindex(',', a.[字段] + ',', b.number) - b.number) from tb a,master..spt_values b where b.number>0 and b.type='p' and substring(',' + a.[字段],b.number,1) = ',' ) select stuff((select ','+字段 from t for xml path('')),1,1,'') as 字段/* 字段 ---------------------- erete,xxx,dd,qwer(1 行受影响) */
楼主把原来的数据和你的sql语句贴出来看看
if not object_id('tb') is null drop table tb Go Create table tb(col nvarchar(11)) Insert tb select 'qwer,xxx,dd' union all select 'qwer,erete' union all select 'erete,dd' union all select 'qwer,dd' Go create FUNCTION dbo.f_mergSTR() RETURNS varchar(50) AS BEGIN DECLARE @t TABLE(ID int identity,b bit) --分拆处理辅助表,由于列col的最大宽度为50,所以只需要1到50的分拆辅助记录 INSERT @t(b) SELECT TOP 50 0 FROM syscolumns DECLARE @r varchar(50) SET @r='' SELECT @r=@r+','+s FROM( SELECT s=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID) FROM tb a,@t b where SUBSTRING(','+a.col,b.ID,1)=',' GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID) )a RETURN(STUFF(@r,1,1,'')) END GO select dbo.f_mergSTR() /* dd,erete,qwer,xxx */
给你点资料 用用 --示例数据 CREATE TABLE tb(ID int,col varchar(50)) INSERT tb SELECT 1,'1,2,3,4' UNION ALL SELECT 1,'1,3,4' UNION ALL SELECT 1,'1,4' UNION ALL SELECT 2,'11,3,4' UNION ALL SELECT 2,'1,33,4' UNION ALL SELECT 3,'1,3,4' GO--1. 字符串并集处理函数 CREATE FUNCTION dbo.f_mergSTR(@ID int) RETURNS varchar(50) AS BEGIN DECLARE @t TABLE(ID int identity,b bit) --分拆处理辅助表,由于列col的最大宽度为50,所以只需要1到50的分拆辅助记录 INSERT @t(b) SELECT TOP 50 0 FROM syscolumns DECLARE @r varchar(50) SET @r='' SELECT @r=@r+','+s FROM( SELECT s=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID) FROM tb a,@t b WHERE a.ID=@ID AND b.ID<=LEN(a.col) AND SUBSTRING(','+a.col,b.ID,1)=',' GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID) )a ORDER BY s RETURN(STUFF(@r,1,1,'')) END GO--调用用户定义实现交并集查询 SELECT ID,col=dbo.f_mergSTR(ID) FROM tb GROUP BY ID GO /*================================================================*/ --2. 字符串交集处理函数 CREATE FUNCTION dbo.f_mergSTR(@ID int) RETURNS varchar(50) AS BEGIN DECLARE @t TABLE(ID int identity,b bit) --分拆处理辅助表,由于列col的最大宽度为50,所以只需要1到50的分拆辅助记录 INSERT @t(b) SELECT TOP 50 0 FROM syscolumns DECLARE @r varchar(50) SET @r='' SELECT @r=@r+','+s FROM( SELECT s=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID) FROM tb a,@t b WHERE a.ID=@ID AND b.ID<=LEN(a.col) AND SUBSTRING(','+a.col,b.ID,1)=',' GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID) HAVING COUNT(*)=(SELECT COUNT(*) FROM tb WHERE ID=@ID))a RETURN(STUFF(@r,1,1,'')) END GO--调用用户定义实现交交集查询 SELECT ID,col=dbo.f_mergStr(ID) FROM tb GROUP BY ID GO
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') 方法一: create function dbo.f_st(@id int) returns nvarchar(4000) as begin declare @a varchar(1000) select @a=isnull(@a+',','')+value from tb where id=@id return @a end SELECT id,value=dbo.f_st(id) from tb group by id 方法二:: select * from(select distinct id from tb) a cross apply( select [value]=STUFF(REPLACE(REPLACE( ( select value from tb as N where id=a.id for XML auto ),'<N value="',','),'"/>',''),1,1,'')
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-07 10:53:16
---------------------------------
--> 生成测试数据表-tbif not object_id('tb') is null
drop table tb
Go
Create table tb([字段] nvarchar(11))
Insert tb
select 'qwer,xxx,dd' union all
select 'qwer,erete' union all
select 'erete,dd' union all
select 'qwer,dd'
Go
--Select * from tb-->SQL查询如下:
with t as
(
select distinct
[字段]=substring(a.[字段], b.number, charindex(',', a.[字段] + ',', b.number) - b.number)
from tb a,master..spt_values b
where b.number>0
and b.type='p'
and substring(',' + a.[字段],b.number,1) = ','
)
select stuff((select ','+字段 from t for xml path('')),1,1,'') as 字段/*
字段
----------------------
erete,xxx,dd,qwer(1 行受影响)
*/
drop table tb
Go
Create table tb(col nvarchar(11))
Insert tb
select 'qwer,xxx,dd' union all
select 'qwer,erete' union all
select 'erete,dd' union all
select 'qwer,dd'
Go
create FUNCTION dbo.f_mergSTR()
RETURNS varchar(50)
AS
BEGIN
DECLARE @t TABLE(ID int identity,b bit)
--分拆处理辅助表,由于列col的最大宽度为50,所以只需要1到50的分拆辅助记录
INSERT @t(b) SELECT TOP 50 0
FROM syscolumns DECLARE @r varchar(50)
SET @r=''
SELECT @r=@r+','+s
FROM(
SELECT s=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
FROM tb a,@t b
where SUBSTRING(','+a.col,b.ID,1)=','
GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
)a
RETURN(STUFF(@r,1,1,''))
END
GO
select dbo.f_mergSTR()
/*
dd,erete,qwer,xxx
*/
--示例数据
CREATE TABLE tb(ID int,col varchar(50))
INSERT tb SELECT 1,'1,2,3,4'
UNION ALL SELECT 1,'1,3,4'
UNION ALL SELECT 1,'1,4'
UNION ALL SELECT 2,'11,3,4'
UNION ALL SELECT 2,'1,33,4'
UNION ALL SELECT 3,'1,3,4'
GO--1. 字符串并集处理函数
CREATE FUNCTION dbo.f_mergSTR(@ID int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @t TABLE(ID int identity,b bit)
--分拆处理辅助表,由于列col的最大宽度为50,所以只需要1到50的分拆辅助记录
INSERT @t(b) SELECT TOP 50 0
FROM syscolumns DECLARE @r varchar(50)
SET @r=''
SELECT @r=@r+','+s
FROM(
SELECT s=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
FROM tb a,@t b
WHERE a.ID=@ID
AND b.ID<=LEN(a.col)
AND SUBSTRING(','+a.col,b.ID,1)=','
GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
)a ORDER BY s
RETURN(STUFF(@r,1,1,''))
END
GO--调用用户定义实现交并集查询
SELECT ID,col=dbo.f_mergSTR(ID)
FROM tb
GROUP BY ID
GO
/*================================================================*/
--2. 字符串交集处理函数
CREATE FUNCTION dbo.f_mergSTR(@ID int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @t TABLE(ID int identity,b bit)
--分拆处理辅助表,由于列col的最大宽度为50,所以只需要1到50的分拆辅助记录
INSERT @t(b) SELECT TOP 50 0
FROM syscolumns DECLARE @r varchar(50)
SET @r=''
SELECT @r=@r+','+s
FROM(
SELECT s=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
FROM tb a,@t b
WHERE a.ID=@ID
AND b.ID<=LEN(a.col)
AND SUBSTRING(','+a.col,b.ID,1)=','
GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
HAVING COUNT(*)=(SELECT COUNT(*) FROM tb WHERE ID=@ID))a
RETURN(STUFF(@r,1,1,''))
END
GO--调用用户定义实现交交集查询
SELECT ID,col=dbo.f_mergStr(ID)
FROM tb
GROUP BY ID
GO
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')
方法一:
create function dbo.f_st(@id int)
returns nvarchar(4000)
as
begin
declare @a varchar(1000)
select @a=isnull(@a+',','')+value from tb where id=@id
return @a
end
SELECT id,value=dbo.f_st(id) from tb group by id
方法二::
select * from(select distinct id from tb) a cross apply(
select [value]=STUFF(REPLACE(REPLACE(
(
select value from tb as N
where id=a.id
for XML auto
),'<N value="',','),'"/>',''),1,1,'')