挑战T-SQL 高手,sql server高手请帮看这个要求能否用sql实现,大送分

Question

写了一上午还没有较好的办法，请大家帮忙了。
Table1表的数据如下:
ID1 ID2
A00001 B00002
A00001 B00003
A00002 B00004
A00002 B00005
A00003 B00005
A00003 B00006
A00003 B00007
......
说明:Table1表的关健字是:ID1+ID2,主要是想得出下面的TYPE这个字段的值，

希望查询出的结果:


ID1 ID2 TYPE
A00001 B00002 1
A00001 B00003 1
A00002 B00004 2
A00002 B00005 2
A00003 B00005 2
A00003 B00006 2
A00003 B00007 2
.....请帮写条语句，求出这个TYPE的值。感谢。

Answer 1

TYPE有什么规律和计算方法？？？？

Answer 2

谢谢楼上的回答，现补充对TYPE的取值做说明：（1）A00001被B00002和B00003使用到，并且B00002和B00003没有被其它ID1使用到，所以第1第2条记录就是一个完全的对应关系，因此TYPE给它同一个值 1，也即它是同一个组；
（2）A00002被B00004和B00005使用到，但B00005又使用了A00003，而A00003使用了B00006和B00007，依此类推....。所以它们是同一个组，给值2。

Answer 3

--SQL2000
Create table T1(ID1 varchar(10), ID2 varchar(10))
Insert into T1  select 'A00001', 'B00002' 
Insert into T1  select 'A00001', 'B00003' 
Insert into T1  select 'A00002' ,'B00004' 
Insert into T1  select 'A00002' ,'B00005' 
Insert into T1  select 'A00003' ,'B00005' 
Insert into T1  select 'A00003' ,'B00006' 
Insert into T1  select 'A00003' ,'B00007'
GOselect * ,type =0  into #T from T1declare @type int , @id1 varchar(10), @id2 varchar(10)
set @type=0
Update #T
set @type= case when id1=@id1 or id2=@id2
                             then @type
                             else @type+1 end,
      @id1=id1,@id2=id2,
       type=@typeselect * from #T
/*
A00001 B00002 1
A00001 B00003 1
A00002 B00004 2
A00002 B00005 2
A00003 B00005 2
A00003 B00006 2
A00003 B00007 2*/
Drop table T1,#T

Answer 4

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID1] varchar(6),[ID2] varchar(6))
go
insert [tb]
select 'A00001','B00002' union all
select 'A00001','B00003' union all
select 'A00002','B00004' union all
select 'A00002','B00005' union all
select 'A00003','B00005' union all
select 'A00003','B00006' union all
select 'A00003','B00007'
select h.[ID1],h.[ID2],f.type
from [tb] h join
(
select [ID1],max(type) as type
from
(
select t.*,
case (select count(1) from [tb] where ID1 <> t.ID1 and [ID2] = t.[ID2]) when 0 then 1 else 2 end as type
from [tb] t
) r
group by [ID1]) f
on h.[ID1] = f.[ID1]
----------------------------------
A00001 B00002 1
A00001 B00003 1
A00002 B00004 2
A00002 B00005 2
A00003 B00005 2
A00003 B00006 2
A00003 B00007 2

Answer 5

--SQL2000
Create table T1(ID1 varchar(10), ID2 varchar(10))
Insert into T1  select 'A00001', 'B00002' 
Insert into T1  select 'A00001', 'B00003' 
Insert into T1  select 'A00002' ,'B00004' 
Insert into T1  select 'A00002' ,'B00005' 
Insert into T1  select 'A00003' ,'B00005' 
Insert into T1  select 'A00003' ,'B00006' 
Insert into T1  select 'A00003' ,'B00007'Insert into T1  select 'A00008' ,'B00008'
GOselect id=identity(int,1,1),ID2
INTO #ID
from T1
group by ID2
select ID1,T1.ID2, type=id
INTO #T
from T1,#ID
where T1.ID2=#ID.ID2declare @type int, @i int
set @i=1
declare c1 cursor for select id from #ID order by id
open c1
fetch next from c1 into @type
while @@fetch_status=0
begin
   if exists(select top 1 1 from #T  where  type=@type)
   begin
             Update #T
              set type=@i
              where exists (select top 1 1 from #T A
where A.type=#T.type
and exists(select top 1 1 from #T B
where B.ID1=A.ID1
and B.type=@type)
                                    )
   end  if exists(select top 1 1 from #T where type!=@i
                                                      and ID1 in (select ID1 from #T where type=@i))
   begin
           select @type=max(type)
from #T
where ID1 in (select ID1 from #T where type=@i)
    end  else
     begin
          set @i=@i+1
          fetch next from c1 into @type
     end
end
close c1
deallocate c1Update #T
set type= (select count(distinct type) from #T A where A.type<=#T.type)select * from #T
/*
ID1      ID2     type
------------------------------------
A00001 B00002 1
A00001 B00003 1
A00002 B00004 2
A00002 B00005 2
A00003 B00005 2
A00003 B00006 2
A00003 B00007 2
A00008 B00008 3*/
Drop table T1,#T,#ID

Answer 6

如果ID1和ID2不会相互交叉（即 ID2中不会出现 Axxx这样的值，而都是Bxxx)，那就可以简单很多

Answer 7

--以前写的，数据多可能会慢，楼主测试下
DECLARE @TB TABLE([KFID] VARCHAR(6), [KFNO] VARCHAR(11))
INSERT @TB 
SELECT 'A00001', 'B00002' UNION ALL 
SELECT 'A00001', 'B00003' UNION ALL 
SELECT 'A00002', 'B00004' UNION ALL 
SELECT 'A00002', 'B00005' UNION ALL 
SELECT 'A00003', 'B00005' UNION ALL 
SELECT 'A00003', 'B00006' UNION ALL 
SELECT 'A00003', 'B00007' 
 CREATE TABLE #([KFID] VARCHAR(6), [KFID2] VARCHAR(6), [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(6), @KFID2 VARCHAR(6),@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 KFID,GRP FROM #DROP TABLE #

Answer 8

--前面理解错了--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID1] varchar(6),[ID2] varchar(6))
go
insert [tb]
select 'A00001','B00002' union all
select 'A00001','B00003' union all
select 'A00002','B00004' union all
select 'A00002','B00005' union all
select 'A00003','B00005' union all
select 'A00003','B00006' union all
select 'A00003','B00007' union all
select 'A00004','B00008' union all
select 'A00004','B00009'
select *,type = 0 into #temp from [tb]declare @i int,@id varchar(6)select @i = 1declare cursor_temp cursor for
select distinct [ID1] from [tb]
open cursor_temp
fetch  next from Cursor_temp into @id 
while @@fetch_status = 0
begin
if exists(select 1 from [tb] where [ID2] in (select [ID2] from [tb] 
where [ID1] = @id) and [ID1] <> @id)
begin
update #temp set type = @i where [ID1] = @id

end
else
begin
update #temp set type = (select max(type)+1 from #temp) where [ID1] = @id
select @i = @i + 1
end
fetch next from Cursor_temp into @id 
end
close Cursor_temp
deallocate Cursor_tempselect * from #tempdrop table #temp
----------------------------------------
A00001 B00002 1
A00001 B00003 1
A00002 B00004 2
A00002 B00005 2
A00003 B00005 2
A00003 B00006 2
A00003 B00007 2
A00004 B00008 3
A00004 B00009 3

Answer 9

吃餐饭后回来一看就已经有几个答案，真是高兴，我先仔细阅读一下。
回答一下playwarcraft朋友的问题，ID1和ID2是会相互交叉的，它们是ID值，其实都是从00000001开始的。稍后若OK再谢谢大家。

Answer 10

谢谢各位，我倾向于nianran520的答案，自认为简洁点（不知道是不是先入为主，呵），我原来是不想用游标，所以来发贴。

Answer 11

   公布结果。 playwarcraft的做法完全正确，nianran520的做法接近正确，主要是未处理第一次出现的情况，在真实环境中运行的结果也不对，原因未查。谢谢各位的帮助。
    playwarcraft的代码在8000条数据真实环境中要跑9分28秒，我在他的基础上做了优化，现在只需要9秒钟。下面是代码，和大家分享。  create table #temp (ID1 varchar(20),ID2 varchar(20))
  insert into #temp
  select 'A00001','B00002' union all
  select 'A00001','B00003' union all
  select 'A00002','B00004' union all
  select 'A00002','B00005' union all
  select 'A00003','B00005' union all
  select 'A00003','B00006' union all
  select 'A00003','B00007' union all
  select 'A00004','B00008' union all
  select 'A00004','B00009' union all
  select 'A00005','B00010' ---union all  
  //modify 1
  CREATE CLUSTERED INDEX C_Idx_temp ON #temp(ID1)  select id=identity(int,1,1),ID2
  INTO #ID
  from #temp
  group by ID2  select A.ID1,B.ID2, B.id As type
INTO #T 
from #temp A
  inner join #ID B
On A.ID2=B.ID2  //modify 2
  CREATE CLUSTERED INDEX C_Idx_t ON #T(ID1)
  create index type_idx on #t (type) declare @type int, @i int
set @i=1
declare c1 cursor for select id from #ID order by id
open c1
fetch next from c1 into @type
while @@fetch_status=0
begin
 if exists(select top 1 1 from #T  where  type=@type)
 begin
Update #T
set type=@i
where exists (select top 1 1 from #T A where A.type=#T.type
and exists( select top 1 1 from #T B
where B.ID1=A.ID1
and B.type=@type))
 end if exists(select top 1 1 from #T where type!=@i
       and ID1 in (select ID1 from #T where type=@i))
begin
 select @type=max(type)
 from #T
 where ID1 in (select ID1 from #T where type=@i)
end
else
begin
set @i=@i+1
fetch next from c1 into @type
end
end
close c1
deallocate c1 
  //modify 3
  update a
  set type=b.xh
  from #t a
  inner join (select type,row_number() over (order by type) as xh from #t
  group by type) b
  on a.type=b.type  select * from #t
  drop table #t
  drop table #ID
  drop table #t