如何完成这个sql? - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

如何完成这个sql?

如何完成这个sql?# Database - 数据库

j*n2003-03-06 08:03

1 楼

我有3张table,
table: team
team_id
aa
bb
table: score
team_id game_id score status
aa 001 8 win
bb 001 5 loss
table: game
game_id
001
现在我要生成这样的结果:
team_1 score_1 status_1 team_2 score_2 status_2 game
aa 8 win bb 5 loss 001
我该怎么写sql？
我试了以下的query, 但是变成结果有2条，为
aa 8 win bb 5 loss 001
bb 5 loss aa 8 win 001
query:
select t1.team_id, s1.score, s1.status,
t2.team_id, s2.score, s2.status, g.game_id
from team t1, team t2, sc

z*g2003-03-06 08:03

2 楼

I am not so sure. but here are my 2 suggestions:
1: Add select DISTINCT
2. in where clause
add : and s1.status='win';

【在 j**n 的大作中提到】

: 我有3张table,
: table: team
: team_id
: aa
: bb
: table: score
: team_id game_id score status
: aa 001 8 win
: bb 001 5 loss
: table: game

f*g2003-03-06 08:03

3 楼

this one may work for this case,
select t1.team_id, s1.score, s1.status,
t2.team_id, s2.score, s2.status, g.game_id
from team t1, team t2, score s1, score s2, game g
where t1.team_id = s1.team_id and
t2.team_id = s2.team_id and
s1.game_id = s2.game_id and
s1.team_id < s2.team_id
and s1.game_id = g.game_id
Note: the last condition make sure we have 4 joins for 5 tables in the WHERE
clause.

【在 j**n 的大作中提到】

: 我有3张table,
: table: team
: team_id
: aa
: bb
: table: score
: team_id game_id score status
: aa 001 8 win
: bb 001 5 loss
: table: game

j*n2003-03-06 08:03

4 楼

thank you, that works great!

【在 f***g 的大作中提到】

:
: this one may work for this case,
: select t1.team_id, s1.score, s1.status,
: t2.team_id, s2.score, s2.status, g.game_id
: from team t1, team t2, score s1, score s2, game g
: where t1.team_id = s1.team_id and
: t2.team_id = s2.team_id and
: s1.game_id = s2.game_id and
: s1.team_id < s2.team_id
: and s1.game_id = g.game_id