先测试数据：
create table testData(id int,var1 nvarchar(20),vargroup nvarchar(800))
go
insert into testData(id,var1,vargroup)values
(1,'Atest1','Atest1 10,Atest2 12,atest3 22,bbb 13'), --第三列中有它的相同族的成员，比如 我和 1，2，3是一个组的。
(2,'Atest2','Atest1 103,Atest2 12,atest3 33,bbb 13'), --PS，相同族名称后面的数字是不定的，是其它值。
(3,'atest3','atest3 33,Atest1 112,Atest2 22,bbb 44'), --继续PS，顺序也是不定的。这三条是一个族，同一族里多出bbb不存在不管
(4,'Btest1','Btest1 23,Btest2 25'),
(5,'Atest1','Btest1 31,Btest2 23'),
(6,'Ctest3','Ctest3 23,Ctest4 545')
期望结果，给它们相同族的编一个族名或编号，结果如下
id var1 vargroup groupNo
----------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Atest1 Atest1 10,Atest2 12,atest3 22,bbb 13 1
2 Atest2 Atest1 103,Atest2 12,atest3 33,bbb 13 1
3 atest3 Atest1 112,Atest2 22,atest3 33,bbb 44 1
4 Btest1 Btest1 23 2
5 Btest1 Btest1 31,Btest2 23 2
6 Ctest3 Ctest3 23 3
------解决方案--------------------
select *,groupid=dense_RANK() over (order by vargroup ) from testdata
------解决方案--------------------
create table testData(id int,var1 nvarchar(10),vargroup nvarchar(80))
insert into testData(id,var1,vargroup)values
(1,'Atest1','Atest1 10,Atest2 12,atest3 22,bbb 13'),
(2,'Atest2','Atest1 103,Atest2 12,atest3 33,bbb 13'),
(3,'atest3','atest3 33,Atest1 112,Atest2 22,bbb 44'),
(4,'Btest1','Btest1 23,Btest2 25'),
(5,'Atest1','Btest1 31,Btest2 23'),
(6,'Ctest3','Ctest3 23,Ctest4 545'),
(7,'CN12434','CN12434 222,EN421324 222'), -- 7,8同组但顺序不定
(8,'EN421324','EN421324 222,CN12434 222')
-- 建函数
create function dbo.fngs
(@x nvarchar(80)) returns nvarchar(80)
as
begin
declare @r nvarchar(80)
declare @t table(x nvarchar(80))
select @x=@x+','
while(charindex(' ',@x,1)>0)
begin
select @x=stuff(@x,charindex(' ',@x,1),charindex(',',@x,charindex(' ',@x,1))-charindex(' ',@x,1),'')
end
insert into @t(x)
select x
from
(select substring(a.s,b.number,charindex(',',a.s+',',b.number)-b.number) 'x'
from (select left(@x,len(@x)-1) 's') a
inner join master.dbo.spt_values b
on b.type='P' and b.number between 1 and len(a.s)
and substring(','+a.s,b.number,1)=',') t
order by x
select @r=isnull(@r,'')+x from @t
return @r
end
-- 查询
select *,
dense_rank() over(order by dbo.fngs(vargroup)) 'groupNo'
from testData
/*
id var1 vargroup groupNo