經典mysql銜接查詢例題。本站提示廣大學習愛好者:(經典mysql銜接查詢例題)文章只能為提供參考,不一定能成為您想要的結果。以下是經典mysql銜接查詢例題正文
MySQL銜接查詢信任年夜家都有所懂得,銜接查詢是在數據庫查詢操作的時刻常常用到的,上面就為您引見MySQL銜接查詢
mysql銜接查詢:支撐多表銜接
對統一張表可以反復銜接屢次(別號在屢次銜接統一張表時很主要)
例題1:
上面有2張表
teams表
競賽成果表:result
成績:
得出一張表:客隊,主隊,競賽成就,競賽時光
辦法一:子查詢和銜接查詢混雜
step1:
select result.id, t_name as h_name,match_time,result from teams join result on teams.t_id=result.h_id
step2:
select result.id ,t_name as g_name from teams join result on teams.t_id=result.g_id
獲得
step3:依據競賽的id 相等銜接以上兩表便可
select t1.id,h_name,g_name,result,match_time from
(select result.id, t_name as h_name,match_time,result from teams join result on teams.t_id=result.h_id) as t1
join
(select result.id ,t_name as g_name from teams join result on teams.t_id=result.g_id) as t2
on t1.id=t2.id;
便可獲得
成果是出來了,有點繁瑣
辦法二:屢次銜接查詢
select result.id,t1.t_name as h_name ,t2.t_name as g_name ,result,match_time from result
join
teams as t1 on result.h_id=t1.t_id
join
teams as t2 on t2.t_id=result.g_id;
便可獲得:
Teams表要銜接2次所以要有別號
例題2:
現有下表 subject
求如許一個表
父欄目名 ,子欄目稱號
銜接查詢
本身銜接本身更須要別號了
select t1.name as p_name,t2.name as son_name from subject as t1 join subject as t2 on t1.id=t2.pid;
便可獲得
以上就是本文的全體內容,願望年夜家可以或許愛好。