根据给出的表结构按要求写出SQL语句。
Match 赛程表字段名称字段类型描述matchIDint
主键hostTeamIDint主队的
IDguestTeamIDint客队的
ID match Result varchar(20)比赛结果
,如(2:0)matchTimedate比赛开始时间Team 参赛队伍表字段名称字段类型描述teamIDint主键teamNamevarchar(20)队伍名称Match的hostTeamID与guestTeamID都与Team中的teamID关联
查出 2006-6-1 到2006-7-1之间举行的所有比赛,并且用以下形式列出:拜仁 2:0 不来梅 2006-6-21
mysql> select * from m;
----- ------ ------ ------ ------------
| mid | hid | gid | mres | matime |
----- ------ ------ ------ ------------
| 1 | 1 | 2 | 2:0 | 2006-05-21 |
| 2 | 2 | 3 | 1:2 | 2006-06-21 |
| 3 | 3 | 1 | 2:5 | 2006-06-25 |
| 4 | 2 | 1 | 3:2 | 2006-07-21 |
----- ------ ------ ------ ------------
4 rows in set (0.00 sec)
mysql> select * from t;
------ ----------
| tid | tname |
------ ----------
| 1 | 国安 |
| 2 | 申花 |
| 3 | 公益联队 |
------ ----------
3 rows in set (0.00 sec)
mysql> select hid,t1.tname as hname ,mres,gid,t2.tname as gname,matime
-> from
-> m left join t as t1 备注:m左链接t=t1
-> on m.hid = t1.tid 条件:m的id = t1的id
-> left join t as t2 左链接 t=t2
-> on m.gid = t2.tid; 条件 m表的gid = t2的tid;
------ ---------- ------ ------ ---------- ------------
| hid | hname | mres | gid | gname | matime |
------ ---------- ------ ------ ---------- ------------
| 1 | 国安 | 2:0 | 2 | 申花 | 2006-05-21 |
| 2 | 申花 | 1:2 | 3 | 公益联队 | 2006-06-21 |
| 3 | 公益联队 | 2:5 | 1 | 国安 | 2006-06-25 |
| 2 | 申花 | 3:2 | 1 | 国安 | 2006-07-21 |
------ ---------- ------ ------ ---------- ------------
4 rows in set (0.00 sec)
select mid,t1.tname as hname ,mres,gid,t2.tname as gteam,matime
from
m inner join t as t1
on m.hid = t1.tid
inner join t as t2
on m.gid = t2.tid
where matime between \'2006-06-01\' and \'2006-07-01\';
思路如下: