求助：相同属性归类问题
本帖最后由 lily0804 于 2013-09-05 16:08:28 编辑 现有一张表Property
内容如下
MemberID P1 P2 P3
1 1A 2B 3A
2 1B 2D 3C
3 1A 2E 3B
4 1C 2A 3D
5 1D 2C 3E
6 1E 2B 3C
P1 P2 P3分别为Member的三个属性
现要求将有任何一个属性相同的Member放到同一组中，结果如下
MemberID GroupID
1 1
3 1
6 1
2 1
4 2
5 3
即Member1的P1和Member3相同，为同一组，P2和Member6相同，6也入组，由于Member6的P3和Member2相同，因此2也入组，以此类推，直到无法找到属性相同的成员为止（请考虑属性是N个的情况）。
[解决办法]
create table #tb(MemberID int ,P1 varchar(10), P2 varchar(10), P3 varchar(10))
insert into #tb
select 1,'1A','2B','3A'
union all select 2,'1B','2D','3C'
union all select 3,'1A','2E','3B'
union all select 4,'1C','2A','3D'
union all select 5,'1D','2C','3E'
union all select 6,'1D','2B','3C'
select MemberID,P1, 1 as groupID
from #tb a
where exists (select 1 from
(select *
from (select *,rn=RANK() over(partition by P1 order by MemberID) from #tb)t
where rn>=2)b where a.P1=b.P1)
union all
select MemberID,P2, 2 as groupID
from #tb a
where exists (select 1 from
(select *
from (select *,rn=RANK() over(partition by P2 order by MemberID) from #tb)t
where rn>=2)b where a.P2=b.P2)
union all
select MemberID,P3, 3 as groupID
from #tb a
where exists (select 1 from
(select *
from (select *,rn=RANK() over(partition by P3 order by MemberID) from #tb)t
where rn>=2)b where a.P3=b.P3)
/*
MemberIDP1groupID
11A1
31A1
51D1
61D1
12B2
62B2
23C3
63C3
*/
create table Property
(MemberID int, P1 varchar(5), P2 varchar(5), P3 varchar(5))
insert into Property
select 1, '1A', '2B', '3A' union all
select 2, '1B', '2D', '3C' union all
select 3, '1A', '2E', '3B' union all
select 4, '1C', '2A', '3D' union all
select 5, '1D', '2C', '3E' union all
select 6, '1E', '2B', '3C'
declare @r table(MemberID int,GroupID int)
declare @gid int,@mid int,@p1 varchar(5),@p2 varchar(5),@p3 varchar(5)
declare ap scroll cursor for select MemberID,P1,P2,P3 from Property
open ap
fetch first from ap into @mid,@p1,@p2,@p3
while(@@fetch_status<>-1)
begin
if not exists(select 1 from @r a,Property b
where a.MemberID=b.MemberID and (b.P1=@p1 or b.P2=@p2 or b.P3=@p3))
begin
select @gid=isnull(@gid,0)+1
insert into @r(MemberID,GroupID) values(@mid,@gid)
while(@@rowcount>0)
begin
insert into @r(MemberID,GroupID)
select a.MemberID,@gid from property a
where a.MemberID not in (select MemberID from @r) and
exists(select 1 from property b
where b.MemberID<>a.MemberID and (b.P1=a.P1 or b.P2=a.P2 or b.P3=a.P3))
end
end
fetch next from ap into @mid,@p1,@p2,@p3
end
close ap
deallocate ap
-- 结果
select MemberID,GroupID from @r
/*
MemberID GroupID
----------- -----------
1 1
2 1
3 1
6 1
4 2
5 3
(6 row(s) affected)
*/
