SQL查询朋友关系圈应用实例

原贴：http://topic.youkuaiyun.com/u/20100412/11/a4ea520e-7dd0-44d2-98bb-9f62f0ed6160.html?21233

 

--------------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-04-14 06:02:36

--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

--          Jul  9 2008 14:43:34

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

--------------------------------------------------------------------------

--> 生成测试数据表:tb

 

IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([callno] INT,[calledno] INT,[groupid] INT)

INSERT [tb]

SELECT 111,1000,1 UNION ALL

SELECT 111,2000,1 UNION ALL

SELECT 222,1000,2 UNION ALL

SELECT 222,4000,2 UNION ALL

SELECT 333,5000,3 UNION ALL

SELECT 333,6000,3 UNION ALL

SELECT 444,4000,4 UNION ALL

SELECT 444,1,4 UNION ALL

SELECT 444,2,4 UNION ALL

SELECT 555,55,5 UNION ALL

SELECT 555,5000,5 UNION ALL

--

SELECT 666,8,6 UNION ALL

SELECT 666,88,6 UNION ALL

SELECT 666,888,6 UNION ALL

SELECT 777,9,7 UNION ALL

SELECT 777,99,7 UNION ALL

SELECT 777,999,7 UNION ALL

SELECT 888,44,8 UNION ALL

SELECT 888,444,8 UNION ALL

SELECT 999,66,9 UNION ALL

SELECT 999,666,9 UNION ALL

SELECT 999,44,9 UNION ALL

SELECT 999,99,9 UNION ALL

SELECT 9999,44,10 UNION ALL

SELECT 9999,8,10 UNION ALL

--

SELECT 1,100,100 UNION ALL

SELECT 1,200,100 UNION ALL

SELECT 2,200,200 UNION ALL

SELECT 2,300,200 UNION ALL

SELECT 3,300,300 UNION ALL

SELECT 3,400,300 UNION ALL

SELECT 4,400,400 UNION ALL

SELECT 4,500,400 UNION ALL

SELECT 5,500,500 UNION ALL

SELECT 5,600,500 UNION ALL

SELECT 6,600,600

GO

--SELECT * FROM [tb]

 

-->SQL查询如下:

 

DECLARE @CALLNO INT,@CALLEDNO INT

DECLARE C CURSOR FOR

    SELECT CALLNO,CALLEDNO FROM T

OPEN C

FETCH NEXT FROM C INTO @CALLNO,@CALLEDNO

WHILE @@FETCH_STATUS = 0

BEGIN

    UPDATE T SET

       GROUPID=(SELECT MIN(GROUPID) FROM T WHERE CALLEDNO=@CALLEDNO)

    WHERE CALLNO=@CALLNO

    FETCH NEXT FROM C INTO @CALLNO,@CALLEDNO

END

CLOSE C

DEALLOCATE C

 

SELECT * FROM T

/*

callno      calledno    groupid

----------- ----------- -----------

111         1000        1

111         2000        1

222         1000        1

222         4000        1

333         5000        3

333         6000        3

444         4000        1

444         1           1

444         2           1

555         55          3

555         5000        3

666         8           6

666         88          6

666         888         6

777         9           7

777         99          7

777         999         7

888         44          8

888         444         8

999         66          7

999         666         7

999         44          7

999         99          7

9999        44          6

9999        8           6

1           100         100

1           200         100

2           200         100

2           300         100

3           300         100

3           400         100

4           400         100

4           500         100

5           500         100

5           600         100

6           600         100

 

(36 行受影响)

*/

 

--沟沟的代码：
declare @groupid int, @rowcount int
set  @groupid= -1
while  exists(select 1 from T where groupid>@groupid)
begin
  select top  1 @groupid= groupid  from T  where groupid>@groupid order by groupid
 
  set @rowcount =@@rowcount
  While @rowcount>0
  BEGIN
  
  Update T
  set groupid= @groupid
  where calledno in(select calledno from T as A where A.groupid=@groupid)
  and groupid<>@groupid
 
  set @rowcount = @@rowcount

   Update T
  set groupid=@groupid
  where callno in (select callno from T as A where A.groupid=@groupid)
  and groupid<>@groupid

  set @rowcount =@rowcount +@@rowcount

  END
end

select * from T
/*
111    1000    1
111    2000    1
222    1000    1
222    4000    1
333    5000    3
333    6000    3
444    4000    1
444    1    1
444    2    1
555    55    3
555    5000    3

666    8    6
666    88    6
666    888    6
777    9    6
777    99    6
777    999    6
888    44    6
888    444    6
999    66    6
999    666    6
999    44    6
999    99    6
9999    44    6
9999    8    6
1    100    100
1    200    100
2    200    100
2    300    100
3    300    100
3    400    100
4    400    100
4    500    100
5    500    100
5    600    100
6    600    100

*/
Drop table T