create table tb (ID int,col varchar(50))
insert tb select 1,'1,2,3,4'
union all select 1,'1,2'
union all select 2,'1,2,3'
union all select 2,'1'
实现为
1 '1,2,3,4'
2 '1,2,3'
消除重复的项即求字符串的并集
insert tb select 1,'1,2,3,4'
union all select 1,'1,2'
union all select 2,'1,2,3'
union all select 2,'1'
实现为
1 '1,2,3,4'
2 '1,2,3'
消除重复的项即求字符串的并集
insert tb select 1, '1,2,3,4'
union all select 1, '1,2'
union all select 2, '1,2,3'
union all select 2, '1' select
t.*
from
tb t
where
not exists(select 1 from tb where ID=t.ID and charindex(t.col,col)>0 and len(col)>len(t.col))/*
ID col
----------- --------------------------------------------------
1 1,2,3,4
2 1,2,3
*/drop table tb
insert @tb select 1, '1,2,3,4 '
union all select 1, '1,2 '
union all select 2, '1,2,3 '
union all select 2, '1 ' select ID,max(col) from @tb a group by ID
/*
(4 row(s) affected)ID
----------- --------------------------------------------------
1 1,2,3,4
2 1,2,3 (2 row(s) affected)
*/
insert tb select 1, '1,2,3,4 '
union all select 1, '1,2 '
union all select 2, '1,2,3 '
union all select 2, '1 ' GOCREATE FUNCTION F_UNION(@ID INT)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @t TABLE ( ID INT IDENTITY(1,1), M BIT)
INSERT INTO @t
SELECT TOP 50 0 FROM SYSOBJECTS DECLARE @re VARCHAR(50)
SELECT @re = ''
SELECT @re = @re + ',' + RTRIM(s) FROM
( SELECT [S] = SUBSTRING(A.COL, B.ID, CHARINDEX(',',A.COL+',',B.ID)-B.ID)
FROM tb A, @t B
WHERE B.ID = CHARINDEX(',',','+A.COL,B.ID) AND B.ID <= LEN(A.COL) AND A.ID = @ID
GROUP BY SUBSTRING(A.COL, B.ID, CHARINDEX(',',A.COL+',',B.ID)-B.ID)
) A ORDER BY s RETURN (STUFF(@re,1,1,'') )
END
GOSELECT ID, COL=DBO.F_UNION(ID) FROM tb GROUP BY ID
DROP TABLE TB
DROP FUNCTION F_UNION
create table tb (ID int,col varchar(50))
insert tb select 1, '1,2,3,4 '
union all select 1, '1,2,5,6'
union all select 2, '1,2,3 '
union all select 2, '1,7'
实现为
1 '1,2,3,4,5,6 '
2 '1,2,3,7'
还有ORDER BY s 这里的s是什么?
insert tb select 1, '1,2,3,4 '
union all select 1, '1,2 '
union all select 2, '1,2,3 '
union all select 2, '1 ' GO CREATE FUNCTION F_UNION(@ID INT)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @t TABLE ( ID INT IDENTITY(1,1), M BIT)
INSERT INTO @t
SELECT TOP 50 0 FROM SYSOBJECTS DECLARE @re VARCHAR(50)
SELECT @re = ' '
SELECT @re = @re + ', ' + RTRIM(s) FROM
( SELECT [S] = SUBSTRING(A.COL, B.ID, CHARINDEX( ', ',A.COL+ ', ',B.ID)-B.ID)
FROM tb A, @t B
WHERE B.ID = CHARINDEX( ', ', ', '+A.COL,B.ID) AND B.ID <= LEN(A.COL) AND A.ID = @ID
GROUP BY SUBSTRING(A.COL, B.ID, CHARINDEX( ', ',A.COL+ ', ',B.ID)-B.ID)
) A ORDER BY s RETURN (STUFF(@re,1,1, ' ') )
END
GO SELECT ID, COL=DBO.F_UNION(ID) FROM tb GROUP BY ID
DROP TABLE TB
DROP FUNCTION F_UNION为什么查SYSOBJECTS这个表
还有ORDER BY s 这里的s是什么?
insert tb select 1, '1,2,3,4'
union all select 1, '1,2'
union all select 2, '1,2,3'
union all select 2, '1' select * from tb where col not in(
select a.col from tb a
inner join tb b
on a.id=b.id and charindex(','+b.col+',',','+a.col+',')=0
)如果这样哪
create table tb (ID int,col varchar(50))
insert tb select 1, '1,2,3,4 '
union all select 1, '1,2,5,6 '
union all select 2, '1,2,3 '
union all select 2, '1,7 '
实现为
1 '1,2,3,4,5,6 '
2 '1,2,3,7 '借助函数实现.
create table #tb_new (ID int,col varchar(8000))
DECLARE cursor_name CURSOR FOR
SELECT ID,col+',' FROM tbOPEN cursor_name
FETCH NEXT FROM cursor_name INTO @ID,@col
WHILE @@FETCH_STATUS = 0
BEGIN
while len(@col)>0
begin
set @single = left(@col,charindex(',',@col)-1)
set @col = replace(@col,@single+',','')
insert #tb select @id,@single
end FETCH NEXT FROM cursor_name INTO @ID,@col
ENDCLOSE cursor_name
DEALLOCATE cursor_name
DECLARE cursor_name CURSOR FOR
SELECT distinct ID FROM #tbOPEN cursor_name
FETCH NEXT FROM cursor_name INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
set @col_new=''
select distinct @col_new=@col_new+col+','
from (select distinct col from #tb where ID=@ID) a
order by col
insert #tb_new select @ID,@col_new
FETCH NEXT FROM cursor_name INTO @ID
ENDCLOSE cursor_name
DEALLOCATE cursor_nameselect ID,LEFT(col,len(col)-1)
from #tb_newdrop table #tb
drop table #tb_new
( SELECT [S] = SUBSTRING(A.COL, B.ID, CHARINDEX( ', ',A.COL+ ', ',B.ID)-B.ID)
FROM tb A, @t B
WHERE B.ID = CHARINDEX( ', ', ', '+A.COL,B.ID) AND B.ID <= LEN(A.COL) AND A.ID = @ID
GROUP BY SUBSTRING(A.COL, B.ID, CHARINDEX( ', ',A.COL+ ', ',B.ID)-B.ID)
) A ORDER BY s 这里的Order BY s 是根据
SELECT [S] = SUBSTRING(A.COL, B.ID, CHARINDEX( ', ',A.COL+ ', ',B.ID)-B.ID)
FROM tb A, @t B
WHERE B.ID = CHARINDEX( ', ', ', '+A.COL,B.ID) AND B.ID <= LEN(A.COL) AND A.ID = @ID
GROUP BY SUBSTRING(A.COL, B.ID, CHARINDEX( ', ',A.COL+ ', ',B.ID)-B.ID)
里合成的s来处理的