篮球比赛SQL智力题解决方法

篮球比赛SQL智力题
一个表为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  null
drop table tb

create 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)a2
union 
select * from (select 'b' as name1) a ,(select name from tb)a2
union
select * from (select 'c' as name1) a ,(select name from tb)a2
union
select * 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 FOR
select name1,name from #
declare @name1 varchar(20),@name varchar(20)
OPEN cur
FETCH NEXT FROM cur into @name1,@name
insert into #2 select @name1,@name
FETCH NEXT FROM cur into @name1,@name
WHILE @@FETCH_STATUS = 0
   BEGIN
    if not exists(select 1 from #2 where name1+name=@name1+@name or name1+name=@name+@name1)
    insert into #2 select @name1,@name
      FETCH NEXT FROM cur into @name1,@name
   END
CLOSE cur
DEALLOCATE cur

select * 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.name
drop table tb

篮球比赛SQL智力题解决方法

相关推荐