合并问题！难！

Question

合并有相同号码的员工 ,比如下面KFID =001 的有2个号码，其中一个号码在KFID=002 里也出现了，我们就把KFID001，002分在同一组中，然后KFID =002 的13500000002又在 KFID =003中出现，那么FID001，002，003应该都分在同一组中 ，以此类推，如何能高效的分组，请教源：
KFID  KFNO          
001   13500000001   
001   13500000002  
002   13500000002  
002   13500000003
003   13500000003
003   13500000004
004   13500000005
005   13500000006最终生成下表
KFID GRPID
001  1
002  1
003  1
004  2
005  3

Answer 1

DECLARE @TB TABLE([KFID] VARCHAR(3), [KFNO] VARCHAR(11))
INSERT @TB 
SELECT '001', '13500000001' UNION ALL 
SELECT '001', '13500000002' UNION ALL 
SELECT '002', '13500000002' UNION ALL 
SELECT '002', '13500000003' UNION ALL 
SELECT '003', '13500000003' UNION ALL 
SELECT '003', '13500000004' UNION ALL 
SELECT '004', '13500000005' UNION ALL 
SELECT '005', '13500000006';WITH CTE AS
(
SELECT A.KFID AS KFID1,A.KFNO AS FKNO1,B.KFID AS KFID2,B.KFNO AS KFNO2,
ROW_NUMBER() OVER (ORDER BY A.KFID,A.KFNO) AS SEQ
FROM @TB AS A LEFT JOIN @TB AS B
  ON A.KFNO=B.KFNO AND A.KFID<>B.KFID
)
,
CTE2 AS
(
SELECT *, 1 AS GRP FROM CTE WHERE SEQ=1
UNION ALL
SELECT C1.*,CASE WHEN C1.KFID1=C2.KFID1 OR C1.KFNO2=C2.KFNO2  THEN GRP ELSE GRP+1 END
FROM CTE AS C1, CTE2 AS C2 
WHERE C1.SEQ=C2.SEQ+1 
)
SELECT DISTINCT KFID1,GRP FROM CTE2 
/*
KFID1 GRP
----- -----------
001   1
002   1
003   1
004   2
005   3
*/

Answer 2

--不嫌效率低的话，跑个cursor吧
create table T(kfid varchar(03), kfno varchar(11))
insert into T select '001',  '13500000001'
insert into T select '001',  '13500000002'  
insert into T select '002' , '13500000002'  
insert into T select '002' , '13500000003' 
insert into T select '003' , '13500000003' 
insert into T select '003' , '13500000004' 
insert into T select '004' , '13500000005' 
insert into T select '005' , '13500000006' GOselect kfid,kfno,
tmp=(select count(distinct kfno) from T where kfno<=A.kfno)
into #temp
from T as Aselect distinct tmp into #id from #tempdeclare @tmp  int, @i int
set @i=1
declare c1 cursor for  select tmp from #id order by tmp
open c1
fetch next from c1 into @tmp
while @@fetch_status=0
begin
   if exists(select top 1 1 from #temp where tmp=@tmp)
        begin
                   update #temp
                   set tmp=@i
                   where  exists(select top 1 1
    from #temp A 
where  A.tmp=#temp.tmp
and exists(select top 1 1 from #temp  B
where B.kfid=A.kfid 
and B.tmp=@tmp)
)                   
         end   if exists(select  top 1  1 from #temp where  tmp!=@i
and kfid in
(select kfid from #temp where tmp= @i))           begin
select @tmp=max(tmp)
from #temp
where  kfid in
(select kfid from #temp where tmp= @i)
           end
  else
    begin
       set @i=@i+1
       fetch next from c1 into @tmp
     end
end
close c1
deallocate c1--查看結果
select kfid, (select count(distinct tmp) from #temp where tmp<=A.tmp) as GRPID
from 
(
select distinct kfid,tmp  from #temp
) as A
/*
kfid      GRPID
-----------------------
001 1
002 1
003 1
004 2
005 3*/
drop table #id
drop table #temp
drop table T

Answer 3

DECLARE @TB TABLE([KFID] VARCHAR(3), [KFNO] VARCHAR(11))
INSERT @TB 
SELECT '001', '13500000001' UNION ALL 
SELECT '001', '13500000002' UNION ALL 
SELECT '001', '13500000007' UNION ALL  --加条数据，3楼的结果就不对，要改改SELECT '002', '13500000002' UNION ALL 
SELECT '002', '13500000003' UNION ALL 
SELECT '003', '13500000003' UNION ALL 
SELECT '003', '13500000004' UNION ALL 
SELECT '004', '13500000005' UNION ALL 
SELECT '005', '13500000006' 

Answer 4

首先十分感谢csdyyr的解答，
不好意思，可能是我给的数据太少，
还有一种情况，会造成结果不是我想要的，比如数据如下001  13500000001  
001  13500000002  
002  13500000002  
002  13500000003 
003  13500000003 
003  13500000004 
004  13500000005 
005  13500000006 
006  13500000006   --数据中还有这样的数据（增加）由于这个对应关系，那么我想的结果是：KFID1 GRP
----- -----------
001   1
002   1
003   1
004   2
005   1  --006的关系，导致005也被归为组1了
006   1也就是说这张表是多对多的关系，行数也不规则，求教

Answer 5

4楼的结果应该是没问题的，（我目前有个报表就是这么搞的，俗称找亲戚）
数据量不大的话，cursor跑跑就可以了，我的才1w不到的数据，所以就用cursor来搞了

Answer 6

--用辅助函数实现
--创建环境
create table tab
(
ID INT IDENTITY(1,1),
KFID VARCHAR(10),
KFNO VARCHAR(20)
)
goinsert into tab select '001',  '13500000001'  
insert into tab select '001',  '13500000002'  
insert into tab select '002',  '13500000002'  
insert into tab select '002',  '13500000003' 
insert into tab select '003',  '13500000003' 
insert into tab select '003',  '13500000004' 
insert into tab select '004',  '13500000005' 
insert into tab select '005',  '13500000006' GO--建立辅助函数
create function f_getid
(
@kfid varchar(10)
)
returns int
as
begin declare @tab table
(
id int,
KFID VARCHAR(10),
KFNO VARCHAR(20)
)

insert into @tab
select id,kfid,kfno
from tab
where kfid = @kfid while (@@rowcount > 0)
begin
insert into @tab
select id,kfid,kfno
from tab
where kfid in (select kfid from tab where kfno in (select kfno from @tab))
  and id not in (select id from @tab)
end declare @id int
select @id = 0

select @id = min(id) from @tab return @id 

end
go--查询
with t1(id,kfid,kfno,nid) as 
(
select *,dbo.f_getid(kfid) as nid
from tab
)
select distinct kfid,NID = (select count(distinct nid) + 1 from t1 where nid < a.nid)
from t1 a--结果
/*
kfid                 NID
-------------------- -----------
13500000001          1
13500000002          1
13500000003          1
13500000004          1
13500000005          2
13500000006          3(6 行受影响)
*/

Answer 7

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([KFID] varchar(3),[KFNO] bigint)
insert [tb]
select '001',13500000001 union all
select '001',13500000002 union all
select '002',13500000002 union all
select '002',13500000003 union all
select '003',13500000003 union all
select '003',13500000004 union all
select '004',13500000005 union all
select '005',13500000006select KFID,(select count(distinct KFID) from [tb] where KFID<=r.KFID)-(select sum(group_id)
from (
select KFID ,case when KFNO in (select KFNO from [tb] where KFID=(select max(KFID) from [tb] 
where KFID<t.KFID)) then 1 else 0 end as group_id
from [tb] t
) h where KFID<=r.KFID) as GRPID
from
(
select KFID ,case when KFNO in (select KFNO from [tb] where KFID=(select max(KFID) from [tb] 
where KFID<t.KFID)) then 1 else 0 end as group_id
from [tb] t
) r
group by KFID--------------------------------
001 1
002 1
003 1
004 2
005 3

Answer 8

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([KFID] varchar(3),[KFNO] bigint)
insert [tb]
select '001',13500000001 union all
select '001',13500000002 union all
select '002',13500000002 union all
select '002',13500000003 union all
select '003',13500000003 union all
select '003',13500000004 union all
select '004',13500000005 union all
select '005',13500000006 union all
select '006',13500000006select KFID,(select count(distinct KFID) from [tb] where KFID<=r.KFID)-(select sum(group_id)
from (
select KFID ,case when KFNO in (select KFNO from [tb] where KFID=(select max(KFID) from [tb] 
where KFID<t.KFID)) then 1 else 0 end as group_id
from [tb] t
) h where KFID<=r.KFID) as GRPID
from
(
select KFID ,case when KFNO in (select KFNO from [tb] where KFID=(select max(KFID) from [tb] 
where KFID<t.KFID)) then 1 else 0 end as group_id
from [tb] t
) r
group by KFID----------------------------------
001 1
002 1
003 1
004 2
005 3
006 3

Answer 9

LS的不对的，，
你加 select '006',13500000004

Answer 10

001  13500000001  
001  13500000002  
001  13500000006     --增加此行  ……还可能更多的号出现在其他KFID里，然后其他里面又有号在其他里，总之可能是个多分支
002  13500000002  
002  13500000003 
003  13500000003 
003  13500000006  
004  13500000005 
005  13500000006 
006  13500000006  playwarcraft 说的差不多~
但是游标效率太低，有没有更好的方法？

Answer 11

我想了下 还有用游标吧~
只不过，可以将取得的KFID放入临时表，尽量少跑~
暂且这样试试~

Answer 12

DECLARE @TB TABLE([KFID] VARCHAR(3), [KFNO] VARCHAR(11))
INSERT @TB 
SELECT '001', '13500000002' UNION ALL 
SELECT '001', '13500000006' UNION ALL 
SELECT '002', '13500000002' UNION ALL 
SELECT '002', '13500000003' UNION ALL 
SELECT '003', '13500000003' UNION ALL 
SELECT '003', '13500000006' UNION ALL 
SELECT '004', '13500000005' UNION ALL 
SELECT '005', '13500000006' UNION ALL 
SELECT '006', '13500000006' CREATE TABLE #([KFID] VARCHAR(3), [KFNO] VARCHAR(11), ID INT IDENTITY(1,1))
INSERT #
SELECT * FROM @TB ORDER BY KFIDCREATE UNIQUE INDEX IX_ID ON #(ID)DECLARE @TB2 TABLE([KFID] VARCHAR(3), [KFNO] VARCHAR(11), GRP INT)
DECLARE @GRP INT, @ID INT
SET @GRP=1
SET @ID=1INSERT @TB2
SELECT KFID,KFNO,@GRP FROM # WHERE ID=@IDWHILE @@ROWCOUNT>0
BEGIN  
  SET @GRP=@GRP+1
  SET @ID=@ID+1  INSERT @TB2
  SELECT TOP 1 T.KFID,T.KFNO,ISNULL(GRP,@GRP)
  FROM # AS T LEFT JOIN @TB2 AS T2
    ON T.KFID=T2.KFID OR T.KFNO=T2.KFNO
  WHERE T.ID=@ID  
END;WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (ORDER BY GRP) AS SEQ FROM @TB2
),
CTE2 AS
(
SELECT *,1 AS GRPID FROM CTE WHERE SEQ=1
UNION ALL
SELECT T.*,CASE WHEN T.GRP=T2.GRP THEN GRPID ELSE GRPID+1 END 
FROM CTE AS T,CTE2 AS T2 
WHERE T.SEQ=T2.SEQ+1
)SELECT DISTINCT KFID,GRPID FROM CTE2DROP TABLE #
/*
KFID GRPID
---- -----------
001  1
002  1
003  1
004  2
005  1
006  1(6 行受影响)
*/

Answer 13

--加个order by 
WHILE @@ROWCOUNT>0
BEGIN  
  SET @GRP=@GRP+1
  SET @ID=@ID+1  INSERT @TB2
  SELECT TOP 1 T.KFID,T.KFNO,ISNULL(GRP,@GRP)
  FROM # AS T LEFT JOIN @TB2 AS T2
    ON T.KFID=T2.KFID OR T.KFNO=T2.KFNO
  WHERE T.ID=@ID 
  ORDER BY T2.KFID DESC
END

Answer 14

感谢csdyyr的回复，但是这似乎也只是解决了某个KFID的KFNO在另外的2个里面的问题，如果有3个，4个……甚至更多，结果就有问题了比如：SELECT '001', '13500000002' UNION ALL 
SELECT '001', '13500000006' UNION ALL 
SELECT '001', '13500000009' UNION ALL 
SELECT '001', '13500000010' UNION ALL 
SELECT '002', '13500000002' UNION ALL 
SELECT '002', '13500000003' UNION ALL 
SELECT '003', '13500000003' UNION ALL 
SELECT '003', '13500000006' UNION ALL 
SELECT '004', '13500000005' UNION ALL 
SELECT '005', '13500000006' UNION ALL 
SELECT '006', '13500000006' UNION ALL 
SELECT '007', '13500000009' UNION ALL 
SELECT '008', '13500000009' UNION ALL 
SELECT '010', '13500000010' 

Answer 15

数据不大的话直接用我4楼写的游标跑跑得了，
这种实现，应该不会是实时的查询用吧？如果是做report的话，不必在乎这一点点performance的浪费。

Answer 16

--楼主再帮测试一下:)
DECLARE @TB TABLE([KFID] VARCHAR(3), [KFNO] VARCHAR(11))
INSERT @TB 
SELECT '001', '13500000002' UNION ALL
SELECT '001', '13500000006' UNION ALL
SELECT '001', '13500000009' UNION ALL
SELECT '001', '13500000010' UNION ALL
SELECT '002', '13500000002' UNION ALL
SELECT '002', '13500000005' UNION ALL
SELECT '003', '13500000003' UNION ALL
SELECT '003', '13500000006' UNION ALL
SELECT '004', '13500000005' UNION ALL
SELECT '005', '13500000006' UNION ALL
SELECT '006', '13500000006' UNION ALL
SELECT '007', '13500000009' UNION ALL
SELECT '008', '13500000009' UNION ALL
SELECT '010', '13500000010' CREATE TABLE #([KFID] VARCHAR(3), [KFNO] VARCHAR(11), [KFID2] VARCHAR(3), [KFNO2] VARCHAR(11), ID INT IDENTITY(1,1))
INSERT #
SELECT T.KFID, T.KFNO, T2.KFID AS KFID2, T2.KFNO AS KFNO2
FROM @TB AS T LEFT JOIN @TB AS T2 
  ON T.KFID<>T2.KFID AND T.KFNO=T2.KFNO
ORDER BY T.KFID,CASE WHEN T2.KFID IS NULL THEN 1 ELSE 0 END,T2.KFIDCREATE UNIQUE INDEX IX_ID ON #(ID)DECLARE @TB2 TABLE([KFID] VARCHAR(3), [KFNO] VARCHAR(11),[KFID2] VARCHAR(3), [KFNO2] VARCHAR(11), GRP INT)
DECLARE @GRP INT, @ID INT
SET @GRP=1
SET @ID=1INSERT @TB2
SELECT KFID,KFNO,KFID2,KFNO2,@GRP FROM # WHERE ID=@IDWHILE @@ROWCOUNT>0
BEGIN  
  SET @GRP=@GRP+1
  SET @ID=@ID+1  INSERT @TB2
  SELECT TOP 1 T.KFID,T.KFNO,T.KFID2,T.KFNO2,ISNULL(GRP,@GRP)
  FROM # AS T LEFT JOIN @TB2 AS T2
    ON T.KFID=T2.KFID OR T.KFNO2=T2.KFNO2 OR T.KFID2=T2.KFID2
  WHERE T.ID=@ID 
  ORDER BY T2.KFID2 DESC
END   --SELECT * FROM #  DROP TABLE #;WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (ORDER BY GRP) AS SEQ FROM @TB2
),
CTE2 AS
(
SELECT *,1 AS GRPID FROM CTE WHERE SEQ=1
UNION ALL
SELECT T.*,CASE WHEN T.GRP=T2.GRP THEN GRPID ELSE GRPID+1 END 
FROM CTE AS T,CTE2 AS T2 
WHERE T.SEQ=T2.SEQ+1
)SELECT DISTINCT KFID,GRPID FROM CTE2DROP TABLE #
/*
KFID GRPID
---- -----------
001  1
002  1
003  1
004  1
005  1
006  1
007  1
008  1
010  1(9 行受影响)
*/

Answer 17

--简化一下
DECLARE @TB TABLE([KFID] VARCHAR(3), [KFNO] VARCHAR(11))
INSERT @TB 
SELECT '001', '13500000001' UNION ALL
SELECT '001', '13500000004' UNION ALL
SELECT '001', '13500000008' UNION ALL
SELECT '001', '13500000010' UNION ALL
SELECT '002', '13500000002' UNION ALL
SELECT '002', '13500000003' UNION ALL
SELECT '003', '13500000003' UNION ALL
SELECT '003', '13500000006' UNION ALL
SELECT '004', '13500000005' UNION ALL
SELECT '005', '13500000001' UNION ALL
SELECT '006', '13500000006' UNION ALL
SELECT '007', '13500000009' UNION ALL
SELECT '008', '13500000009' UNION ALL
SELECT '010', '13500000001' 
 CREATE TABLE #([KFID] VARCHAR(3), [KFID2] VARCHAR(3), [KFNO2] VARCHAR(11), GRP INT, ID INT IDENTITY(1,1))
INSERT #
SELECT T.KFID, T2.KFID AS KFID2, T2.KFNO AS KFNO2, 0 AS GRP
FROM @TB AS T LEFT JOIN @TB AS T2 
  ON T.KFID<>T2.KFID AND T.KFNO=T2.KFNO
ORDER BY T.KFID,CASE WHEN T2.KFID IS NULL THEN 1 ELSE 0 END,T2.KFID
--SELECT * FROM #CREATE CLUSTERED INDEX IX_KFID ON #(KFID)
CREATE INDEX IX_KFID2 ON #(KFID2)
CREATE INDEX IX_KFNO2 ON #(KFNO2)
CREATE UNIQUE INDEX IX_ID ON #(ID)DECLARE @GRP INT, @ID INT, @KFID VARCHAR(3), @KFID2 VARCHAR(3),@KFNO2 VARCHAR(11), @ROWCNT INT, @MAXGRP INT
SET @GRP=1
SET @MAXGRP=1
SET @ID=1SELECT @KFID=KFID FROM # WHERE ID=@ID
UPDATE # SET GRP=@GRP WHERE KFID=@KFID
SET @ROWCNT=@@ROWCOUNTWHILE @ROWCNT>0
BEGIN  
  SET @ID=@ID+@ROWCNT
  
  SELECT TOP 1 @MAXGRP=CASE WHEN T2.GRP IS NULL THEN @MAXGRP+1 ELSE @MAXGRP END, 
               @GRP=ISNULL(T2.GRP, @MAXGRP), 
               @KFID=T.KFID
  FROM # AS T LEFT JOIN # AS T2
    ON (T.KFID2=T2.KFID2 OR T.KFNO2=T2.KFNO2) AND T2.KFID<T.KFID
  WHERE T.ID=@ID
  ORDER BY T2.KFID
  
  UPDATE # SET GRP=@GRP WHERE KFID=@KFID AND GRP=0
  SET @ROWCNT=@@ROWCOUNT
END 
SELECT DISTINCT KFID,GRP FROM #DROP TABLE #
/*
KFID GRP
---- -----------
001  1
002  2
003  2
004  3
005  1
006  2
007  4
008  4
010  1(9 行受影响)
*/