declare @str as varchar(8000)
set @str='1,5,1@2,5,3@3,5@4,5@5,5'上面只是一个例子,像这样一个字符串(长度不定),
以@为界,可以分成好几组,每个组中以,为界
又可以取到每个元素(个数不定)
像这个例子的话,可以分成
1,5,1
2,5,3
3,5
4,5
5,5
五组,只取五个组中共同的部分 也就是5(这个值只有一个,不用过多考虑)这个值要在查询中,作为条件的select * from Table where Field=@结果该如何写语句呢
set @str='1,5,1@2,5,3@3,5@4,5@5,5'上面只是一个例子,像这样一个字符串(长度不定),
以@为界,可以分成好几组,每个组中以,为界
又可以取到每个元素(个数不定)
像这个例子的话,可以分成
1,5,1
2,5,3
3,5
4,5
5,5
五组,只取五个组中共同的部分 也就是5(这个值只有一个,不用过多考虑)这个值要在查询中,作为条件的select * from Table where Field=@结果该如何写语句呢
会mysql的麻烦也给写法吧
declare @str as varchar(8000)
set @str='1,5,1@2,5,3@3,5@4,5@5,5' declare @table table(id varchar(20))
declare @ret varchar(30)
declare @i intselect @str = @str +'@'
select @i = len(@str) - len(replace(@str,'@',''))while charindex('@',@str) >0
begin
select @ret = substring(@str,1,charindex('@',@str)-1)+',' while charindex(',',@ret) >0
begin
insert into @table select substring(@ret,1,charindex(',',@ret)-1)
select @ret = stuff(@ret,1,charindex(',',@ret),'')
end
select @str = stuff(@str,1,charindex('@',@str),'')end
select id from @table group by id having count(1) >= @i --方法2
declare @str as varchar(8000)
set @str='1,5,1@2,5,3@3,5@4,5@5,5' select id
from
(
select substring(r.id,t.number,
charindex(',',r.id+',',t.number)-t.number) as id
from
(
select substring(@str,number,
charindex('@',@str+'@',number)-number) as id
from master.dbo.spt_values
where type = 'p'
and substring('@' + @str,number,1)='@'
) r,master.dbo.spt_values t
where t.type = 'p'
and substring(',' + r.id,t.number,1)=','
) f
group by id
having count(1) >= len(@str) - len(replace(@str,'@',''))+1-----------------------
5
declare @str as varchar(8000)
declare @sql varchar(8000)
set @str='1,5,1@2,5,3@3,5@4,5@5,5' create table #temp(id varchar(20))select @sql = replace(replace(@str,'@',' union all select '),',',' union all select ')select @sql = 'select '+@sqlinsert into #temp
exec(@sql)
select id from #temp
group by id
having count(1) >= len(@str) - len(replace(@str,'@','')) + 1drop table #temp
----------------------------IF object_id('dbo.Fun_Split') is not null
drop function dbo.Fun_Split
ALTER function Fun_Split(@str varchar(8000))
returns varchar(100)
as
begin
declare @table table(id varchar(20))
declare @ret varchar(30)
declare @i int
select @str = @str +'@'
select @i = len(@str) - len(replace(@str,'@',''))
while charindex('@',@str) >0
begin
select @ret = substring(@str,1,charindex('@',@str)-1)+','
while charindex(',',@ret) >0
begin
insert into @table select substring(@ret,1,charindex(',',@ret)-1)
select @ret = stuff(@ret,1,charindex(',',@ret),'')
end
select @str = stuff(@str,1,charindex('@',@str),'')
end
return (select id from @table group by id having count(1) >= @i )
enddeclare @Table table
(
ID int
,value varchar(13)
)insert into @Table
select 1,'xxxx'
union
select 2,'gsgsdgs'
union
select 3,'xx44xx'
union
select 4,'gs45345gsdgs'
union
select 5,'rasjflkasjo'
union
select 6,'gsgsdgs'
union
select 7,'asfashn'
union
select 8,'gsgnvbnvbsdgs'declare @str as varchar(8000)
set @str='1,5,1@2,5,3@3,5@4,5@5,5' select * from @Table where ID=dbo.Fun_Split(@str)
拆分表:--> --> (Roy)生成測試數據
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select
a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID)
from
Tab a,#Num b
where
charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number)
from
Tab a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
substring(','+a.COl2,b.number,1)=','
SQL2005用Xml:select
a.COl1,b.Col2
from
(select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
(select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:;with roy as
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果:
/*
Col1 COl2
----------- -----
1 a
1 b
1 c
2 d
2 e
3 f
*/
Appending:--实验环境 Sql Server 2005 DECLARE @TypeIDs as varchar(8000)
SET @TypeIDs ='1,5,1@2,5,3@3,5@4,5@5,5' DECLARE @TypeIDList table(TypeID nvarchar(30))DECLARE @iCount_TypeID int
DECLARE @ilength_TypeID int
DECLARE @TypeID nvarchar(30)SET @iCount_TypeID = 1
WHILE @iCount_TypeID <> 0
BEGIN
SET @iCount_TypeID = PATINDEX('%@%',@TypeIDs)
SET @ilength_TypeID = CASE @iCount_TypeID WHEN 0 THEN DATALENGTH(@TypeIDs) ELSE @iCount_TypeID-1 END SET @TypeID = SUBSTRING(@TypeIDs,1,@ilength_TypeID) IF NOT EXISTS(SELECT 1 FROM @TypeIDList WHERE TypeID = @TypeID)
BEGIN
INSERT INTO @TypeIDList
VALUES(@TypeID)
END SET @TypeIDs = SUBSTRING(@TypeIDs,@iCount_TypeID+1,DATALENGTH(@TypeIDs))
END-- 测试
SELECT *
FROM @TypeIDList
--简单的测试用例
--SET @TypeIDs ='1,8,5@2,5,3@3,5@4,5@5,5@5,8@9,6,10,5'
--两个以上的测试用例
SET @TypeIDs ='1,8,5,6@2,6,5,3@3,6,5@4,6,5@5,6,5@5,6,8@9,6,10,5' DECLARE @TypeIDList table(TypeID nvarchar(30),id int IDENTITY(1,1))DECLARE @TypeInsideIDs as varchar(8000)
DECLARE @TypeInsideIDList table(TypeInsideID nvarchar(30),Group_id int )DECLARE @iCount_TypeID int
DECLARE @ilength_TypeID int
DECLARE @TypeID nvarchar(30)DECLARE @iCount_TypeInsideID int
DECLARE @ilength_TypeInsideID int
DECLARE @TypeInsideID nvarchar(30)SET @iCount_TypeID = 1DECLARE @Group_id intWHILE @iCount_TypeID <> 0
BEGIN
SET @iCount_TypeID = PATINDEX('%@%',@TypeIDs)
SET @ilength_TypeID = CASE @iCount_TypeID WHEN 0 THEN DATALENGTH(@TypeIDs) ELSE @iCount_TypeID-1 END SET @TypeID = SUBSTRING(@TypeIDs,1,@ilength_TypeID) IF NOT EXISTS(SELECT 1 FROM @TypeIDList WHERE TypeID = @TypeID)
BEGIN
INSERT INTO @TypeIDList
VALUES(@TypeID)
END SET @TypeInsideIDs = @TypeID
PRINT @TypeInsideIDs SELECT @Group_id = id FROM @TypeIDList SET @iCount_TypeInsideID = 1
WHILE @iCount_TypeInsideID <> 0
BEGIN
SET @iCount_TypeInsideID = PATINDEX('%,%',@TypeInsideIDs)
SET @ilength_TypeInsideID = CASE @iCount_TypeInsideID WHEN 0 THEN DATALENGTH(@TypeInsideIDs) ELSE @iCount_TypeInsideID-1 END SET @TypeInsideID = SUBSTRING(@TypeInsideIDs,1,@ilength_TypeInsideID) BEGIN
INSERT INTO @TypeInsideIDList
VALUES(@TypeInsideID,@Group_id)
END
SET @TypeInsideIDs = SUBSTRING(@TypeInsideIDs,@iCount_TypeInsideID+1,DATALENGTH(@TypeInsideIDs))
END
SET @TypeIDs = SUBSTRING(@TypeIDs,@iCount_TypeID+1,DATALENGTH(@TypeIDs))
END-- 测试@分割的字符串
SELECT *
FROM @TypeIDList--测试,分割的字符串
SELECT *
FROM @TypeInsideIDList--用Rank和DENSE_RANK的区别,DENSE_RANK重复时加1,而Rank则否
SELECT TypeInsideID,Group_id,RANK()OVER(PARTITION BY TypeInsideID ORDER BY Group_id)AS 'Rank'
FROM @TypeInsideIDList--选出,号分割中重复的TypeInsideID的值
SELECT TypeInsideID,Group_id --Group_id列去掉即可
FROM
(
SELECT TypeInsideID,Group_id,DENSE_RANK()OVER(PARTITION BY TypeInsideID ORDER BY Group_id)AS 'Rank'
FROM @TypeInsideIDList
)A
WHERE A.Rank =(SELECT COUNT(DISTINCT Group_id) FROM @TypeInsideIDList) --Result:TypeInsideID Group_id
5 7
6 7