if not object_id('tb') is null drop table tb Go Create table tb([name] nvarchar(2),[id] int) Insert tb select N'张三',1 union all select N'李四',1 union all select N'张三',2 union all select N'王五',2 union all select N'王五',3 union all select N'赵六',3 union all select N'赵六',4 union all select N'钱七',4 Go Select * from tbselect distinct name,id=(select min(id) from tb) from tbname id 李四 1 钱七 1 王五 1 张三 1 赵六 1
if not object_id('tb') is null drop table tb Go Create table tb([name] nvarchar(2),[id] int) Insert tb select N'张三',1 union all select N'李四',1 union all select N'张三',2 union all select N'王五',2 union all select N'王五',3 union all select N'赵六',3 union all select N'赵六',4 union all select N'钱七',4 union all select N'刘七',7 Go Select * from tbdrop table # select row=identity(int,1,1),tb.* into # from tb declare @tb table(row int,name varchar(100),id int) declare @name varchar(100),@id int,@row int,@oldid int,@firstid int declare cur cursor for select row, name,id from # open cur fetch from cur into @row,@name,@id insert into @tb select @row,@name,@id set @firstid=@id while @@fetch_status=0 begin print cast(@row as varchar)+' '+@name+' '+cast(@id as varchar) if exists(select 1 from @tb t where name=@name or id=@id) or @oldid=@id begin insert into @tb select @row,@name,@firstid end else begin insert into @tb select @row,@name,@id end set @oldid=@id
fetch next from cur into @row,@name,@id end close cur deallocate cur select * from @tb
declare @tb table(name varchar(100),id int) declare @name varchar(100),@id int,@oldid int,@firstid int declare cur cursor for select name,id from tb open cur fetch from cur into @name,@id insert into @tb select @name,@id set @firstid=@id while @@fetch_status=0 begin
if exists(select 1 from @tb t where name=@name or id=@id) or @oldid=@id begin insert into @tb select @name,@firstid end else begin insert into @tb select @name,@id end set @oldid=@id
fetch next from cur into @name,@id end close cur deallocate cur select name,id from @tb
--> 生成测试数据: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] CREATE TABLE [tb] (name VARCHAR(10),gid INT) INSERT INTO [tb] SELECT '张三',1 UNION ALL SELECT '李四',1 UNION ALL SELECT '张三',2 UNION ALL SELECT '王五',2 UNION ALL SELECT '王五',3 UNION ALL SELECT '赵六',3 UNION ALL SELECT '赵六',4 UNION ALL SELECT '钱七',4 UNION ALL SELECT '刘七',7--SQL查询如下:GOCREATE FUNCTION dbo.GetRelationName(@gid INT) RETURNS @T TABLE(Name VARCHAR(10)) AS BEGIN DECLARE @tb1 TABLE(Name VARCHAR(10),gid INT,level INT);
DECLARE @level INT; SET @level=1;
INSERT @tb1 SELECT name,gid,@level FROM tb WHERE gid=@gid;
WHILE @@ROWCOUNT>0 BEGIN SET @level=@level+1;
INSERT @tb1 SELECT name,gid,@level FROM tb AS A WHERE gid IN(SELECT B.gid FROM tb AS B JOIN @tb1 AS C ON (B.name=C.name AND C.level=@level-1 AND B.gid<C.gid AND B.gid<>@gid)) END
------------------
DECLARE @tb2 TABLE(Name VARCHAR(10),gid INT,level INT); SET @level=1;
INSERT @tb2 SELECT name,gid,@level FROM @tb1;
WHILE @@ROWCOUNT>0 BEGIN SET @level=@level+1;
INSERT @tb2 SELECT name,gid,@level FROM tb AS A WHERE gid IN(SELECT B.gid FROM tb AS B JOIN @tb2 AS C ON (B.name=C.name AND C.level=@level-1 AND B.gid>C.gid AND B.gid<>@gid)); END
INSERT INTO @T SELECT name FROM(SELECT name FROM @tb1 UNION SELECT name FROM @tb2) AS A;
RETURN; ENDGO -- SELECT * FROM (SELECT DISTINCT gid FROM tb) AS A CROSS APPLY dbo.GetRelationName(A.gid) AS B;GO DROP FUNCTION dbo.GetRelationName DROP TABLE tb;/* gid Name ----------- ---------- 1 李四 1 钱七 1 王五 1 张三 1 赵六 2 李四 2 钱七 2 王五 2 张三 2 赵六 3 李四 3 钱七 3 王五 3 张三 3 赵六 4 李四 4 钱七 4 王五 4 张三 4 赵六 7 刘七(21 行受影响) */
if not object_id('tb') is null drop table tb go Create table tb([name] nvarchar(2),[id] int) Insert tb SELECT '张三',1 UNION ALL SELECT '李四',1 UNION ALL SELECT '张三',2 UNION ALL SELECT '王五',2 UNION ALL SELECT '王五',3 UNION ALL SELECT '赵六',3 UNION ALL SELECT '赵六',4 UNION ALL SELECT '钱七',4 UNION ALL SELECT '刘七',7 UNION ALL SELECT '毛久',8 ------------------------------------------- --简单点的 --只能查某个人同组的所有联络人。 -------------------------------------------DECLARE @i INT SET @i=1 DECLARE @t TABLE ([name] nvarchar(2),[id] int,lvl int) INSERT INTO @t SELECT name,id,@i FROM tb WHERE name='张三' --只能查某个人同组的所有联络人。WHILE @@ROWCOUNT>0 BEGIN SET @i=@i+1 INSERT INTO @t SELECT *,@i FROM tb a WHERE EXISTS ( SELECT * FROM @t b WHERE (id=a.id OR name=a.name) AND NOT EXISTS ( SELECT * FROM @t WHERE name=a.name AND id=a.id ) )
if not object_id('tb') is null drop table tb Go Create table tb([name] nvarchar(2),[id] int) Insert tb select N'张三',1 union all select N'李四',1 union all select N'张三',2 union all select N'王五',2 union all select N'王五',3 union all select N'赵六',3 union all select N'赵六',4 union all select N'钱七',4 union all select N'刘七',7 Go Select * from tbdrop table # select row=identity(int,1,1),tb.* into # from tb declare @tb table(row int,name varchar(100),id int) declare @name varchar(100),@id int,@row int,@oldid int,@firstid int declare cur cursor for select row, name,id from # open cur fetch from cur into @row,@name,@id insert into @tb select @row,@name,@id set @firstid=@id while @@fetch_status=0 begin print cast(@row as varchar)+' '+@name+' '+cast(@id as varchar) if exists(select 1 from @tb t where name=@name or id=@id) or @oldid=@id begin insert into @tb select @row,@name,@firstid end else begin insert into @tb select @row,@name,@id end set @oldid=@id
fetch next from cur into @row,@name,@id end close cur deallocate cur select * from @tb
把昨天修改了下,然后添加点数据,加入了一些其他组的数据,貌似比昨天的效果能好一点if not object_id('tb') is null drop table tb Go Create table tb([name] nvarchar(2),[id] int) Insert tb select N'张三',1 union all select N'李四',1 union all select N'张三',2 union all select N'王五',2 union all select N'王五',3 union all select N'赵六',3 union all select N'赵六',4 union all select N'钱七',4 union all select N'刘七',7 union all select N'张七',7 union all select N'张七',8 union all select N'王七',8 union all select N'王一',9 union all select N'刘七',9 union all select N'柴八',9 Go Select * from tbdrop table # select row=identity(int,1,1),tb.*,flag=0 ,[group]=0 into # from tb declare @row int,@name varchar(10),@id int,@group int set @group=1 set @row=1 update # set flag=1,[group]=1 where name =(select name from # where row=1) or id=1while @@rowcount>0 begin set @row=@row+1 select @name=name,@id=id from # where row=@row if exists (select 1 from (select name,id from # where flag=1) k where (name=@name or id=@id) ) begin update t set flag=1,[group]=@group from # t where row=@row end else begin update # set flag=1,[group]=@group+1 where name =@name update t set flag=1,[group]=@group, @group=@group+1 from # t where row=@row end end select name,[group] from #name group 张三 1 李四 1 张三 1 王五 1 王五 1 赵六 1 赵六 1 钱七 1 刘七 2 张七 2 张七 2 王七 2 王一 2 刘七 2 柴八 2
if not object_id('tb') is null
drop table tb
Go
Create table tb([name] nvarchar(2),[id] int)
Insert tb
select N'张三',1 union all
select N'李四',1 union all
select N'张三',2 union all
select N'王五',2 union all
select N'王五',3 union all
select N'赵六',3 union all
select N'赵六',4 union all
select N'钱七',4
Go
Select * from tbselect distinct name,id=(select min(id) from tb)
from tbname id
李四 1
钱七 1
王五 1
张三 1
赵六 1
李四 1
张三 2
王五 2张三和李四在一个组里!但是张三和王五也在一个组里!那么他们三个人都是一个组
go
if not object_id('tb') is null
drop table tb
Go
Create table tb([name] nvarchar(2),[id] int)
Insert tb
select N'张三',1 union all
select N'李四',1 union all
select N'张三',2 union all
select N'王五',2 union all
select N'王五',3 union all
select N'赵六',3 union all
select N'赵六',4 union all
select N'钱七',4 union all
select N'刘七',7
Go
Select * from tbdrop table #
select row=identity(int,1,1),tb.* into # from tb
declare @tb table(row int,name varchar(100),id int)
declare @name varchar(100),@id int,@row int,@oldid int,@firstid int
declare cur cursor for select row, name,id from #
open cur
fetch from cur into @row,@name,@id
insert into @tb select @row,@name,@id
set @firstid=@id
while @@fetch_status=0
begin
print cast(@row as varchar)+' '+@name+' '+cast(@id as varchar)
if exists(select 1 from @tb t where name=@name or id=@id) or @oldid=@id
begin
insert into @tb
select @row,@name,@firstid
end
else
begin
insert into @tb
select @row,@name,@id
end
set @oldid=@id
fetch next from cur into @row,@name,@id
end
close cur
deallocate cur
select * from @tb
declare @tb table(name varchar(100),id int)
declare @name varchar(100),@id int,@oldid int,@firstid int
declare cur cursor for select name,id from tb
open cur
fetch from cur into @name,@id
insert into @tb select @name,@id
set @firstid=@id
while @@fetch_status=0
begin
if exists(select 1 from @tb t where name=@name or id=@id) or @oldid=@id
begin
insert into @tb
select @name,@firstid
end
else
begin
insert into @tb
select @name,@id
end
set @oldid=@id
fetch next from cur into @name,@id
end
close cur
deallocate cur
select name,id from @tb
name id
张三 1
张三 1
李四 1
张三 1
王五 1
王五 1
赵六 1
赵六 1
钱七 1
刘七 7
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (name VARCHAR(10),gid INT)
INSERT INTO [tb]
SELECT '张三',1 UNION ALL
SELECT '李四',1 UNION ALL
SELECT '张三',2 UNION ALL
SELECT '王五',2 UNION ALL
SELECT '王五',3 UNION ALL
SELECT '赵六',3 UNION ALL
SELECT '赵六',4 UNION ALL
SELECT '钱七',4 UNION ALL
SELECT '刘七',7--SQL查询如下:GOCREATE FUNCTION dbo.GetRelationName(@gid INT)
RETURNS @T TABLE(Name VARCHAR(10))
AS
BEGIN
DECLARE @tb1 TABLE(Name VARCHAR(10),gid INT,level INT);
DECLARE @level INT;
SET @level=1;
INSERT @tb1 SELECT name,gid,@level FROM tb WHERE gid=@gid;
WHILE @@ROWCOUNT>0
BEGIN
SET @level=@level+1;
INSERT @tb1
SELECT name,gid,@level
FROM tb AS A
WHERE gid IN(SELECT B.gid
FROM tb AS B
JOIN @tb1 AS C
ON (B.name=C.name
AND C.level=@level-1
AND B.gid<C.gid
AND B.gid<>@gid))
END
------------------
DECLARE @tb2 TABLE(Name VARCHAR(10),gid INT,level INT); SET @level=1;
INSERT @tb2 SELECT name,gid,@level FROM @tb1;
WHILE @@ROWCOUNT>0
BEGIN
SET @level=@level+1;
INSERT @tb2
SELECT name,gid,@level
FROM tb AS A
WHERE gid IN(SELECT B.gid
FROM tb AS B
JOIN @tb2 AS C
ON (B.name=C.name AND C.level=@level-1
AND B.gid>C.gid AND B.gid<>@gid));
END
INSERT INTO @T
SELECT name
FROM(SELECT name FROM @tb1 UNION SELECT name FROM @tb2) AS A;
RETURN;
ENDGO
--
SELECT *
FROM (SELECT DISTINCT gid FROM tb) AS A
CROSS APPLY dbo.GetRelationName(A.gid) AS B;GO
DROP FUNCTION dbo.GetRelationName
DROP TABLE tb;/*
gid Name
----------- ----------
1 李四
1 钱七
1 王五
1 张三
1 赵六
2 李四
2 钱七
2 王五
2 张三
2 赵六
3 李四
3 钱七
3 王五
3 张三
3 赵六
4 李四
4 钱七
4 王五
4 张三
4 赵六
7 刘七(21 行受影响)
*/
if not object_id('tb') is null
drop table tb
go
Create table tb([name] nvarchar(2),[id] int)
Insert tb
SELECT '张三',1 UNION ALL
SELECT '李四',1 UNION ALL
SELECT '张三',2 UNION ALL
SELECT '王五',2 UNION ALL
SELECT '王五',3 UNION ALL
SELECT '赵六',3 UNION ALL
SELECT '赵六',4 UNION ALL
SELECT '钱七',4 UNION ALL
SELECT '刘七',7 UNION ALL
SELECT '毛久',8 -------------------------------------------
--简单点的
--只能查某个人同组的所有联络人。
-------------------------------------------DECLARE @i INT
SET @i=1
DECLARE @t TABLE ([name] nvarchar(2),[id] int,lvl int)
INSERT INTO @t
SELECT name,id,@i FROM tb WHERE name='张三' --只能查某个人同组的所有联络人。WHILE @@ROWCOUNT>0
BEGIN
SET @i=@i+1
INSERT INTO @t
SELECT *,@i
FROM tb a
WHERE EXISTS
(
SELECT * FROM @t b
WHERE (id=a.id OR name=a.name)
AND NOT EXISTS
(
SELECT * FROM @t
WHERE name=a.name
AND id=a.id
)
)
ENDSELECT DISTINCT name FROM @t
还是比较麻烦,用最繁琐的游标写了下
--> (让你望见影子的墙)生成测试数据,时间:2009-04-03
if not object_id('tb') is null
drop table tb
Go
Create table tb([name] nvarchar(2),[id] int)
Insert tb
select N'张三',1 union all
select N'李四',1 union all
select N'张三',2 union all
select N'王五',2 union all
select N'王五',3 union all
select N'赵六',3 union all
select N'赵六',4 union all
select N'钱七',4 union all
select N'刘七',7
Go
Select * from tbdrop table #
select row=identity(int,1,1),tb.* into # from tb
declare @tb table(row int,name varchar(100),id int)
declare @name varchar(100),@id int,@row int,@oldid int,@firstid int
declare cur cursor for select row, name,id from #
open cur
fetch from cur into @row,@name,@id
insert into @tb select @row,@name,@id
set @firstid=@id
while @@fetch_status=0
begin
print cast(@row as varchar)+' '+@name+' '+cast(@id as varchar)
if exists(select 1 from @tb t where name=@name or id=@id) or @oldid=@id
begin
insert into @tb
select @row,@name,@firstid
end
else
begin
insert into @tb
select @row,@name,@id
end
set @oldid=@id
fetch next from cur into @row,@name,@id
end
close cur
deallocate cur
select * from @tb
drop table tb
Go
Create table tb([name] nvarchar(2),[id] int)
Insert tb
select N'张三',1 union all
select N'李四',1 union all
select N'张三',2 union all
select N'王五',2 union all
select N'王五',3 union all
select N'赵六',3 union all
select N'赵六',4 union all
select N'钱七',4 union all
select N'刘七',7 union all
select N'张七',7 union all
select N'张七',8 union all
select N'王七',8 union all
select N'王一',9 union all
select N'刘七',9 union all
select N'柴八',9
Go
Select * from tbdrop table #
select row=identity(int,1,1),tb.*,flag=0 ,[group]=0 into # from tb
declare @row int,@name varchar(10),@id int,@group int
set @group=1
set @row=1
update #
set flag=1,[group]=1
where name =(select name from # where row=1) or id=1while @@rowcount>0
begin
set @row=@row+1
select @name=name,@id=id from # where row=@row
if exists (select 1 from (select name,id from # where flag=1) k where (name=@name or id=@id) )
begin
update t
set flag=1,[group]=@group
from # t
where row=@row
end
else
begin
update #
set flag=1,[group]=@group+1
where name =@name
update t
set flag=1,[group]=@group,
@group=@group+1
from # t
where row=@row
end
end
select name,[group] from #name group
张三 1
李四 1
张三 1
王五 1
王五 1
赵六 1
赵六 1
钱七 1
刘七 2
张七 2
张七 2
王七 2
王一 2
刘七 2
柴八 2