sqlserver面试题

[Guo]

一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.

[叶子]

declare @department table (name varchar(1))
insert into @department
select 'A' union all
select 'B' union all
select 'C' union all
select 'D'

select A.name+' vs '+b.name
from @department a cross join 
@department b where a.name<>b.name
--分主客场吧？
/*
B vs A
C vs A
D vs A
A vs B
C vs B
D vs B
A vs C
B vs C
D vs C
A vs D
B vs D
C vs D
*/

[叶子]

declare @department table (name varchar(1))
insert into @department
select 'A' union all
select 'B' union all
select 'C' union all
select 'D'

select A.name+' vs '+b.name
from @department a cross join 
@department b where a.name<b.name
/*
A vs B
A vs C
B vs C
A vs D
B vs D
C vs D
*/

这样就是单次的了。

[Guo]

谢谢了 第一次提问，没想到这么快得到了解决 哈哈哈哈

[--小F--]

if OBJECT_ID('tb') is not null
drop table tb 
go
create table tb(name varchar(4))
insert into tb select 'a'
union all
select 'b'
union all
select 'c'
union all
select 'd'


select
   a.name+b.name,
   c.name+d.name
from
    tb a,tb  b,tb c, tb d
where
    a.name<>b.name 
and
    a.name<>c.name
and
    a.name<>d.name
and
    b.name<>c.name
and
    b.name<>d.name
and
    c.name<>d.name 
    
    /*ad       cb
ac       db
ad       bc
ab       dc
ac       bd
ab       cd
bd       ca
bc       da
bd       ac
ba       dc
bc       ad
ba       cd
cd       ba
cb       da
cd       ab
ca       db
cb       ad
ca       bd
dc       ba
db       ca
dc       ab
da       cb
db       ac
da       bc

(24 行受影响)*/
    

[vivai2010]

select 'a' x into #tb
union
select 'b' 
union
select 'c' 
union
select 'd' 
go

select  a.x x1,b.x x2 from #tb a,#tb b where a.x<>b.x
go
--or
select  a.x x1,b.x x2 from #tb a,#tb b where a.x<b.x
go

[打一壶酱油]

[Quote=引用 2 楼 maco_wang 的回复:]
SQL code

declare @department table (name varchar(1))
insert into @department
select 'A' union all
select 'B' union all
select 'C' union all
select 'D'

select A.name+' vs '+b.name
from @dep……
[/Quote]

+1