--如果存在数据,就表示有重复. create table tb(id varchar(10))declare @str varchar(8000) set @str='1,2,3,4,5,6,7,8,55'set @str='insert into tb select '+replace(@str,',',' union all select ')exec(@str)select id from tb group by id having count(*) > 1drop table tb
create table tb(id varchar(10))declare @str varchar(8000) set @str='1,2,3,4,5,6,7,8,55'set @str='insert into tb select '+replace(@str,',',' union all select ')exec(@str)--如果存在数据,就表示有重复. select id from tb group by id having count(*) > 1 --各数 select count(*) from tb drop table tb
用一个函数: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_splitSTR] GO--3.2.1 循环截取法 CREATE FUNCTION f_splitSTR( @s varchar(8000), --待分拆的字符串 @split varchar(10) --数据分隔符 )RETURNS @re TABLE(col varchar(100)) AS BEGIN DECLARE @splitlen int SET @splitlen=LEN(@split+'a')-2 WHILE CHARINDEX(@split,@s)>0 BEGIN INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1)) SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'') END INSERT @re VALUES(@s) RETURN END GOselect col from dbo.f_splitSTR(@str,',') group by col having count(1)>1
select top 1000 id=identity(int,1,1) into # from sysobjects a,syscolumns bdeclare @s varchar(100) set @s='1,2,3,4,5'if(SELECT count(distinct CAST(SUBSTRING(@s,ID,CHARINDEX(',',@s+',',ID)-ID) as varchar(100))) FROM # WHERE ID<=LEN(@s+'a') AND CHARINDEX(',',','+@s,ID)=ID)<>(SELECT count( CAST(SUBSTRING(@s,ID,CHARINDEX(',',@s+',',ID)-ID) as varchar(100))) FROM # WHERE ID<=LEN(@s+'a') AND CHARINDEX(',',','+@s,ID)=ID) begin print '有重复的数字' end else if (SELECT count( CAST(SUBSTRING(@s,ID,CHARINDEX(',',@s+',',ID)-ID) as varchar(100))) FROM # WHERE ID<=LEN(@s+'a') AND CHARINDEX(',',','+@s,ID)=ID) not between 5 and 30 begin print '没有在 5 与 30 之间' end else begin print '没有重复的数字,且数字的个数为5到30.' enddrop table #
set @str='1,2,3,4,5,6,7,8,55'set @str='select '+replace(@str,',',' union all select ')exec(@str)
create table tb(id varchar(10))declare @str varchar(8000)
set @str='1,2,3,4,5,6,7,8,55'set @str='insert into tb select '+replace(@str,',',' union all select ')exec(@str)select id from tb group by id having count(*) > 1drop table tb
set @str='1,2,3,4,5,6,7,8,55'set @str='insert into tb select '+replace(@str,',',' union all select ')exec(@str)--如果存在数据,就表示有重复.
select id from tb group by id having count(*) > 1
--各数
select count(*) from tb drop table tb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GOselect col from dbo.f_splitSTR(@str,',') group by col having count(1)>1
set @s='1,2,3,4,5'if(SELECT count(distinct CAST(SUBSTRING(@s,ID,CHARINDEX(',',@s+',',ID)-ID) as varchar(100)))
FROM #
WHERE ID<=LEN(@s+'a')
AND CHARINDEX(',',','+@s,ID)=ID)<>(SELECT count( CAST(SUBSTRING(@s,ID,CHARINDEX(',',@s+',',ID)-ID) as varchar(100)))
FROM #
WHERE ID<=LEN(@s+'a')
AND CHARINDEX(',',','+@s,ID)=ID)
begin
print '有重复的数字'
end
else if (SELECT count( CAST(SUBSTRING(@s,ID,CHARINDEX(',',@s+',',ID)-ID) as varchar(100)))
FROM #
WHERE ID<=LEN(@s+'a')
AND CHARINDEX(',',','+@s,ID)=ID) not between 5 and 30
begin
print '没有在 5 与 30 之间'
end
else
begin
print '没有重复的数字,且数字的个数为5到30.'
enddrop table #