class_info
id | class_name |
2 | s204 |
5 | s205 |
1 | s207 |
7 | s203 |
match_info
id | host_id | guest_id | match_time | match_result |
1 | 2 | 1 | 2018-12-10 | 45:62 |
2 | 2 | 7 | 2018-12-16 | 55:50 |
3 | ||||
4 | ||||
5 |
1、题目需求:已知某学校组织篮球比赛,现在有两张表class_info 和 match_info,需要通过查询获得如下格式表格信息:
host | guest | match_time | match_result |
s204 | s207 | 2018-12-10 | 45:62 |
s204 | s203 | 2018-12-16 | 55:50 |
2、分析:利用左连接查询,同时根据实际情况,match_info需要连接class_info两次(可以多次使用left join连接),才能将match_info中的host_id和guest_id转化为class_name。
3、操作:
select c.class_name as host,d.class_name as guest, m.match_time,m.match_result
from match_info as m left join class_info as c
on m.host_id=c.id
left join class_info as d on m.guest_id=d.id;
4、总结:
犯错一:表的别名重复问题
【注意】在进行多次连接同一个表时,一定不能存在表的重名。解决方法为给重名的表设置不同的表名:例 table_a as t_a