select id,
gp1,
p1 =(select top 1 p1 from 表 group by gp1),
gp2=(select top 1 gp2 from 表 group by gp1),
p1 =(select top 1 p2 from 表 group by gp1)
from 表
group by gp1,id
gp1,
p1 =(select top 1 p1 from 表 group by gp1),
gp2=(select top 1 gp2 from 表 group by gp1),
p1 =(select top 1 p2 from 表 group by gp1)
from 表
group by gp1,id
insert #t
select '01', 'a', '03', 'k' union all
select '01', 'h', '04', 'j' union all
select '01', 'p', '06', 'd' union all
select '02', 'j', '03', 'w' union all
select '02', 'u', '04', 'c' union all
select '03', 'g', '04', 'q' union all
select '06', 'n', '07', 'h'declare @t table(id int,GP1 varchar(2),P1 varchar(1),GP2 varchar(2),P2 varchar(1))
declare @maxid int,@i int
select @i=1,@maxid=(select max(id) from #t)
insert @t select * from #t where id=1
while @i<=@maxid
begin
set @i=@i+1
insert @t
select * from #t
where id=@i
and gp1 not in (select gp1 from @t)
and gp2 not in (select gp2 from @t)
end
select * from @tdrop table #t/*
id GP1 P1 GP2 P2
----------- ---- ---- ---- ----
1 01 a 03 k
5 02 u 04 c
7 06 n 07 h
*/
declare @maxid int,@i int
select @i=1,@maxid=(select max(id) from #t)
insert @t select * from #t where id=1
while @i<=@maxid
begin
set @i=@i+1
insert @t
select * from #t
where id=@i
and gp1 not in (select gp1 from @t union all select gp2 from @t)
and gp2 not in (select gp2 from @t union all select gp1 from @t)
end
select * from @t不知道还有没有其它方法?
的条件值得考虑 要是不连续的ID很多 间距很大就进行了过多的循环 我觉得该用总行数作为循环条件 where里面加上ID不重复的条件 每次用TOP提取一行 这样效率高些
declare @t table(id int,GP1 varchar(2),P1 varchar(1),GP2 varchar(2),P2 varchar(1))
declare @maxid int,@i int
select @i=1,@maxid=(select count(*) from #t)
insert @t select top 1 * from #t
while @i<=@maxid
begin
insert @t
select top 1 * from #t
where id not in(select id from @t)
and gp1 not in (select gp1 from @t)
and gp2 not in (select gp2 from @t)
set @i=@i+1
end
select * from @t
declare @t table(id int,GP1 varchar(2),P1 varchar(1),GP2 varchar(2),P2 varchar(1))
declare @maxid int,@i int,@rc int
select @i=1,@maxid=(select max(id) from #t),@rc=(select count(id) from #t)
insert @t select * from #t where id=1
while @i<=@rc
begin
set @i=@i+1
if (@i>@maxid) break
insert @t
select * from #t
where id=@i
and gp1 not in (select gp1 from @t union select gp1=gp2 from @t)
and gp2 not in (select gp2 from @t union select gp2=gp1 from @t)
end
select * from @t当然如果表没有ID,也可用游标实现