100分求助-两表sql查询问题

[jimmy980]

MATH表
mathId(int)
zhuduiId(int)
keduiId(int)
bifen
date
-------------------
NAME表
nameId(int)
name
-----------------------

其中 zhuduiId与keduiId都与name表的nameId关联

求怎样查出结果如: 不来梅 2:0 切尔西 2006-11-22

很简单,但我不会,急用 ,在线等

[paoluo]

Create Table MATH
(mathId int,
zhuduiId int,
keduiId int,
bifen Varchar(10),
[date] Varchar(10))
Create Table [NAME]
(nameId int,
[name] Nvarchar(20))
Insert MATH
Select 1, 1, 2, '2:0', '2006-11-22'
Insert [NAME] Select 1, N'不来梅'
Union All Select 2, N'切尔西'
GO
Select
B.[name] As zhuduiId,
A.bifen,
C.[name] As keduiId,
[date]
From
MATH A
Inner Join
[NAME] B
On A.zhuduiId = B.nameId
Inner Join
[NAME] C
On A.keduiId = C.nameId
GO
Drop Table MATH, [NAME]
--Result
/*
zhuduiId bifen keduiId date
不来梅 2:0 切尔西 2006-11-22
*/

[playwarcraft]

select b.[name] ,a.bifen,c.[name],a.[date]
from math a
inner join [name] b on a.zhuduiId=b.nameId
inner join [name] c on a.keduiId=c.nameId

[toddzst]

哈哈，我觉得就是这样分，以上2位就是高

[子陌红尘]

select
a.mathId,
max(case a.zhuduiId when b.nameId then b.name end) as zhudui,
a.bifen,
max(case a.keduiId when b.nameId then b.name end) as kedui,
a.date
from
MATH a,NAME b
group by
mathId,bifen,date

[子陌红尘]

select
a.mathId,
max(case a.zhuduiId when b.nameId then b.name end) as zhudui,
a.bifen,
max(case a.keduiId when b.nameId then b.name end) as kedui,
a.date
from
MATH a,NAME b
group by
a.mathId,a.bifen,a.date

[wangdehao]

select (select name from [NAME] where nameId=a.zhuduiId)[主队],a.bifen, (select name from [NAME] where nameId=a.keduiId)[客队],a.date
from math a

[edsoft]

select a.name,b.bifen,b.date,c.name
from math b,name a,name c
where a.nameid=b.zhuduiid and c.nameid=b.keduiid

[edsoft]

哪里有那么复杂？

[free_pop2k]

select mathId,(select name from [Name] where nameID=a.zhuduiId),
(select [name] from [name] where nameid=a.keduiId),bifen,date from MATH a

[kenny_appleso]

select b.[name] ,a.bifen,c.[name],a.[date]
from math a
inner join [name] b on a.zhuduiId=b.nameId
inner join [name] c on a.keduiId=c.nameId