本文目錄一覽：
MySQL 多表查詢
我不是很了解你想要的是怎麼樣。以下是我的思路：select A.survey_id, A.topic, A.qid, B.options, B.description from survey_single_choice as A left join survey_single_choice_option as B on A.id = B.single_choice_id 查出單選
select A1.survey_id, A1.topic, A1.qid, B1.options, B1.description from survey_multiple_choices as A1 left join survey_multiple_choices_option as B1 on A1.id = B1.single_choice_id 查出多選
select S.name, S.description, S.status, C1.topic, C1.qid, C1.options, C1.description from survey as S left join (select A.survey_id, A.topic, A.qid, B.options, B.description from survey_single_choice as A left join survey_single_choice_option as B on A.id = B.single_choice_id) as C1 on S.id = C1.survey_id 單選合併到問卷
select S2.name, S2.description, S2.status, C2.topic, C2.qid, C2.options, C2.description from survey as S2 left join (select A1.survey_id, A1.topic, A1.qid, B1.options, B1.description from survey_multiple_choices as A1 left join survey_multiple_choices_option as B1 on A1.id = B1.single_choice_id) as C2 on S2.id = C2.survey_id 多選合併到問卷
select S3.name, S3.description, S3.status, B2.topic, B2.qid from survey as S3 left join survey_short_answer as B2 on S3.id = B2.survey_id 簡答表合併到問卷
如果你是要多行列出 問卷名 題號 題目select S.name, S.description, S.status, C1.topic, C1.qid from survey as S left join (select A.survey_id, A.topic, A.qid, B.options, B.description from survey_single_choice as A left join survey_single_choice_option as B on A.id = B.single_choice_id) as C1 on S.id = C1.survey_idUNION ALLselect S2.name, S2.description, S2.status, C2.topic, C2.qid from survey as S2 left join (select A1.survey_id, A1.topic, A1.qid, B1.options, B1.description from survey_multiple_choices as A1 left join survey_multiple_choices_option as B1 on A1.id = B1.multiple_choices) as C2 on S2.id = C2.survey_idUNION ALLselect S3.name, S3.description, S3.status, B2.topic, B2.qid from survey as S3 left join survey_short_answer as B2 on S3.id = B2.survey_idORDER BY name, qid ASC
如果是一條列出select * from (select * from (select S.name, S.description, S.status, C1.topic, C1.qid, C1.options, C1.description from survey as S left join (select A.survey_id, A.topic, A.qid, B.options, B.description from survey_single_choice as A left join survey_single_choice_option as B on A.id = B.single_choice_id) as C1 on S.id = C1.survey_id) as D left join (select S2.name, S2.description, S2.status, C2.topic, C2.qid, C2.options, C2.description from survey as S2 left join (select A1.survey_id, A1.topic, A1.qid, B1.options, B1.description from survey_multiple_choices as A1 left join survey_multiple_choices_option as B1 on A1.id = B1.single_choice_id) as C2 on S2.id = C2.survey_id) as D1 on D.name = D1.name) as E left join (select S3.name, S3.description, S3.status, B2.topic, B2.qid from survey as S3 left join survey_short_answer as B2 on S3.id = B2.survey_id) E1 on E.name = E1.name
（注意：要修改*號列出你想列的列名，並改一下選項裡面的列名）
“mysql ”多表聯合查詢語句怎麼寫？
一使用SELECT子句進行多表查詢SELECT 字段名 FROM 表1，表2 … WHERE 表1字段 = 表2字段 AND 其它查詢條件SELECT a.id,a.name,a.address,a.date,b.math,b.english,b.chinese FROM tb_demo065_tel AS b,tb_demo065 AS a WHERE a.id=b.id
注:在上面的的代碼中，以兩張表的id字段信息相同作為條件建立兩表關聯，但在實際開發中不應該這樣使用，最好用主外鍵約束來實現。
1、聯合查詢可合併多個相似的選擇查詢的結果集。等同於將一個表追加到另一個表，從而實現將兩個表的查詢組合到一起，使用謂詞為UNION或UNION ALL。聯合查詢時，查詢結果的列標題為第一個查詢語句的列標題。因此，要定義列標題必須在第一個查詢語句中定義。要對聯合查詢結果排序時，也必須使用第一查詢語句中的列名、列標題或者列序號。
2、在使用UNION 運算符時，應保證每個聯合查詢語句的選擇列表中有相同數量的表達式，並且每個查詢選擇表達式應具有相同的數據類型，或是可以自動將它們轉換為相同的數據類型。在自動轉換時，對於數值類型，系統將低精度的數據類型轉換為高精度的數據類型。
3、在包括多個查詢的UNION語句中，其執行順序是自左至右，使用括號可以改變這一執行順序。例如:查詢1 UNION (查詢2 UNION 查詢3)。
mysql數據庫，多個表的查詢操作
要點：left
join,right
join,inner
join
首先有如下兩個表：
student：
id（int）
name(nvarchar)
1
a
2
b
3
c
4
d
5
e
6
f
quiz:
id(int)
score(int)
1
60
2
70
4
80
6
90
8
100
9
30
內連接：（inner
join）包括連接表的匹配行
select
student.name,quiz.score
from
quiz
inner
join
student
on
student.id=quiz.id
name
score
a
60
b
70
d
80
f
90
左連接：（left
join）包括連接表匹配行以及左連接表的所有行
select
student.name,quiz.score
from
student
left
join
quiz
on
student.id=quiz.id
name
score
a
60
b
70
c
null
d
80
e
null
f
90
右連接：(right
join)結果包括連接表的匹配行以及右連接表的所有行
select
student.name,quiz.score
from
student
right
join
quiz
on
student.id=quiz.id
name
score
a
60
b
70
d
80
f
90
null
100
null
30
當然，也可以看出左連接也可以寫成右連接的形式：
select
student.name,quiz.score
from
student
right
join
quiz
on
student.id=quiz.id等價於
select
student.name,quiz.score
from
quiz
left
join
student
on
student.id=quiz.id
原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-hant/n/152893.html
微信掃一掃 
支付寶掃一掃 