一个表为A 有一个字段name（为球队名）
球队为a，b，c，d
每一个球队都与其它三个球队各比赛一次。
要求构造一个SQL语句查询所有的排列组合。
各位大侠帮帮忙啊。
谢谢！
------解决方案--------------------
- SQL code
select a.name,b.name from A a ,A b where a.name < b.name
------解决方案--------------------
- SQL code
if object_id('tb') is not nulldrop table tbcreate table tb(name varchar(1))insert into tb select 'a'insert into tb select 'b'insert into tb select 'c'insert into tb select 'd'select id=identity(int,1,1),* into # from( select * from (select 'a' as name1) a ,(select name from tb)a2union select * from (select 'b' as name1) a ,(select name from tb)a2unionselect * from (select 'c' as name1) a ,(select name from tb)a2unionselect * from (select 'd' as name1) a ,(select name from tb)a2)tp where name1<>name create table #2(name1 varchar(20),name varchar(20))DECLARE cur CURSOR FORselect name1,name from #declare @name1 varchar(20),@name varchar(20)OPEN curFETCH NEXT FROM cur into @name1,@nameinsert into #2 select @name1,@nameFETCH NEXT FROM cur into @name1,@nameWHILE @@FETCH_STATUS = 0 BEGIN if not exists(select 1 from #2 where [email protected][email protected] or [email protected][email protected]) insert into #2 select @name1,@name FETCH NEXT FROM cur into @name1,@name ENDCLOSE curDEALLOCATE curselect * from #2
------解决方案--------------------
create table tb(name varchar(1))
- SQL code
insert into tb select 'a'insert into tb select 'b'insert into tb select 'c'insert into tb select 'd'select a.name as a,b.name as b from tb a,tb b where a.name<b.name order by a.namedrop table tb