為了求解問題時思路清晰，建議先分列查詢，再將列合併到一個表中，這樣相當於將複雜問題拆解為簡單問題，一一解決。優點是避免所有問題混在一起，程式碼邏輯清晰，可遷移性強，下次遇到類似的查詢問題能快速求解，缺點是程式碼看起來不夠簡潔，存在程式碼冗餘的問題。
一、適用場景和方法
（1）適用場景
考慮查詢過程中是否存在以下情況：
- 查詢某些列時需要分組才能得到，某些列不需要分組就能得到；
- 查詢某些列時需要where條件，某些列不需要where條件；
- 查詢這些列時需要多次用到不同的表；
- 某一個列或幾個列的查詢過程很複雜。
存在上述情況時候，為了求解問題時思路清晰，建議先分列查詢，再將列合併到一個表中，這樣相當於將複雜問題拆解為簡單問題，一一解決。
（2）方法
MySQL多表查詢，將查詢到的列合併到一個表中使用join函式
具體包括：
| 連線型別（四者選一） | 連線條件（三者選一） |
|---|---|
| left join | natural |
| right join | on <連線條件> |
| inner join | using(col1,col2,...,coln) |
| full outer join |
根據查詢需要使用不同的連線型別和條件。其中col指列名（注意兩個表的該列名必須相同）。
二、案例分析
案例來自：SQL135 每個6/7級使用者活躍情況
現有使用者資訊表user_info（uid使用者ID，nick_name暱稱, achievement成就值, level等級, job職業方向, register_time註冊時間）：
| id | uid | nick_name | achievement | level | job | register_time |
|---|---|---|---|---|---|---|
| 1 | 1001 | 牛客1號 | 3100 | 7 | 演演算法 | 2020-01-01 10:00:00 |
| 2 | 1002 | 牛客2號 | 2300 | 7 | 演演算法 | 2020-01-01 10:00:00 |
| 3 | 1003 | 牛客3號 | 2500 | 7 | 演演算法 | 2020-01-01 10:00:00 |
| 4 | 1004 | 牛客4號 | 1200 | 5 | 演演算法 | 2020-01-01 10:00:00 |
| 5 | 1005 | 牛客5號 | 1600 | 6 | C++ | 2020-01-01 10:00:00 |
| 6 | 1006 | 牛客6號 | 2600 | 7 | C++ | 2020-01-01 10:00:00 |
試卷資訊表examination_info（exam_id試卷ID, tag試卷類別, difficulty試卷難度, duration考試時長, release_time釋出時間）：
| id | exam_id | tag | difficulty | duration | release_time |
|---|---|---|---|---|---|
| 1 | 9001 | SQL | hard | 60 | 2021-09-01 06:00:00 |
| 2 | 9002 | C++ | easy | 60 | 2021-09-01 06:00:00 |
| 3 | 9003 | 演演算法 | medium | 80 | 2021-09-01 10:00:00 |
試卷作答記錄表exam_record（uid使用者ID, exam_id試卷ID, start_time開始作答時間, submit_time交卷時間, score得分）：
| uid | exam_id | start_time | submit_time | score |
|---|---|---|---|---|
| 1001 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 78 |
| 1001 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 81 |
| 1005 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:30:01 | 85 |
| 1005 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:02 | 85 |
| 1006 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:21:59 | 84 |
| 1006 | 9001 | 2021-09-07 10:01:01 | 2021-09-07 10:21:01 | 81 |
| 1002 | 9001 | 2020-09-01 13:01:01 | 2020-09-01 13:41:01 | 81 |
| 1005 | 9001 | 2021-09-01 14:01:01 | (NULL) | (NULL) |
題目練習記錄表practice_record（uid使用者ID, question_id題目ID, submit_time提交時間, score得分）：
| uid | question_id | submit_time | score |
|---|---|---|---|
| 1001 | 8001 | 2021-08-02 11:41:01 | 60 |
| 1004 | 8001 | 2021-08-02 19:38:01 | 70 |
| 1004 | 8002 | 2021-08-02 19:48:01 | 90 |
| 1001 | 8002 | 2021-08-02 19:38:01 | 70 |
| 1004 | 8002 | 2021-08-02 19:48:01 | 90 |
| 1006 | 8002 | 2021-08-04 19:58:01 | 94 |
| 1006 | 8003 | 2021-08-03 19:38:01 | 70 |
| 1006 | 8003 | 2021-08-02 19:48:01 | 90 |
| 1006 | 8003 | 2020-08-01 19:38:01 | 80 |
請統計每個6/7級使用者總活躍月份數、2021年活躍天數、2021年試卷作答活躍天數、2021年答題活躍天數，按照總活躍月份數、2021年活躍天數降序排序。由示例資料結果輸出如下：
| uid | act_month_total | act_days_2021 | act_days_2021_exam | act_days_2021_question |
|---|---|---|---|---|
| 1006 | 3 | 4 | 1 | 3 |
| 1001 | 2 | 2 | 1 | 1 |
| 1005 | 1 | 1 | 1 | 0 |
| 1002 | 1 | 0 | 0 | 0 |
| 1003 | 0 | 0 | 0 | 0 |
解釋：6/7級使用者共有5個，其中1006在202109、202108、202008共3個月活躍過，2021年活躍的日期有20210907、20210804、20210803、20210802共4天，2021年在試卷作答區20210907活躍1天，在題目練習區活躍了3天。
分析是否適用‘列拼接成表’的方法：
根據題目要求可知，總活躍月份數、2021年活躍天數和 2021年試卷作答活躍天數、2021年答題活躍天數，查詢所用的表不一樣，並且每一個列的查詢過程都很複雜，所以採取分列查詢再合併列的方法。
分析思路
難點：
1.建立合併列的思想
（1）統計使用者總活躍月份數 如果日期重複算一個月份
[使用]：[年月]：date_format(exrp,'%y%m') ; 去重：distinct
（2）統計使用者2021年活躍天數 如果日期重複算一天
[使用]：[2021年]: year(exrp) = 2021; [年月日]：date(exrp) ; 去重：distinct；
注意： 判斷是否是2021年應該放在select裡面而不是where中
（3）統計2021年試卷作答活躍天數
[使用]： [2021年]: year(exrp) = 2021; [年月日]：date(exrp) ;
（4）統計2021年答題活躍天數
[使用]：多表連線使用 join using( )
（5）合併列
[使用]： [2021年]: year(exrp) = 2021; [年月日]：date(exrp) ;
最終結果
select 查詢結果 [總活躍月份數; 2021年活躍天數; 2021年試卷作答活躍天數; 2021年答題活躍天數]
from 從哪張表中查詢資料[多個join連線的表]
where 查詢條件 [level等級是6/7]
order by 對查詢結果排序 [按照總活躍月份數、2021年活躍天數降序];
實現過程
（1）需要一個臨時表：
with
main as(
#試卷作答記錄和題目練習記錄
select distinct
a.uid,
date(start_time) as days,
'exam' as tag
from user_info a
left join exam_record b
using(uid)
union
select distinct
a.uid,
date(submit_time) as days,
'question' as tag
from user_info a
left join practice_record c
using(uid)
)
注意：mysql版本在8.0之前不支援with。如需配置mysql的8.0版本參考
（2）求select列
- 總活躍月份數
#總活躍月份數 attr
select
uid,
count(distinct date_format(days,'%y%m')) as act_month_total
from main
group by uid
- 2021年活躍天數
#2021年試卷作答活躍天數 attr1
select
uid,
count(distinct(if(year(start_time) = 2021,start_time,null))) as act_days_2021_exam
from main
group by uid
-
2021年試卷作答活躍天數
-
count(distinct(if(year(date(act_date)) = 2021 and tag = 'exam',act_date,null)))利用tag標記是試卷作答記錄還是答題作答記錄。
#2021年試卷作答活躍天數 attr2
select
uid,
count(distinct(if(year(days) = 2021 and tag = 'exam',days,null))) as act_days_2021_exam
from main1
group by uid
- 2021年答題活躍天數
#2021年答題活躍天數 attr3
select
uid,
count(distinct(if(year(days) = 2021 and tag = 'question', days, null))) as act_days_2021_question
from main1
group by uid
（3）合併列
select
a.uid,
act_month_total,
act_days_2021,
act_days_2021_exam,
act_days_2021_question
from user_info a
left join attr using(uid)
left join attr1 using(uid)
left join attr2 using(uid)
left join attr3 using(uid)
where level between 6 and 7
order by act_month_total desc,act_days_2021 desc
求解程式碼
方法一：
使用 with
with
main as(
#試卷作答記錄和題目練習記錄
select distinct
a.uid,
date(start_time) as days,
'exam' as tag
from user_info a
left join exam_record b
using(uid)
union
select distinct
a.uid,
date(submit_time) as days,
'question' as tag
from user_info a
left join practice_record c
using(uid)
)
#合併列
select
a.uid,
act_month_total,
act_days_2021,
act_days_2021_exam,
act_days_2021_question
from user_info a
left join(
#總活躍月份數指的是所有年
select
uid,
count(distinct date_format(days,'%y%m')) as act_month_total
from main
group by uid
) attr using(uid)
left join(
#2021年活躍天數
select
uid,
count(distinct if(year(days) = 2021,days,null)) as act_days_2021
from main
group by uid
) attr1 using(uid)
left join(
#2021年試卷作答活躍天數
select
uid,
count(distinct(if(year(days) = 2021 and tag = 'exam',days,null))) as act_days_2021_exam
from main
group by uid
) attr2 using(uid)
left join(
#2021年答題活躍天數
select
uid,
count(distinct(if(year(days) = 2021 and tag = 'question',days,null))) as act_days_2021_question
from main
group by uid
) attr3 using(uid)
where level between 6 and 7
order by act_month_total desc,act_days_2021 desc#按照總活躍月份數、2021年活躍天數降序排序
方法二：
不使用 with
select
uid,
count(distinct date_format(days,'%y%m')) as act_month_total,#總活躍月份數指的是所有年
count(distinct if(year(days) = 2021,days,null)) as act_days_2021,#2021年活躍天數
count(distinct(if(year(days) = 2021 and tag = 'exam',days,null))) as act_days_2021_exam,#2021年試卷作答活躍天數
count(distinct(if(year(days) = 2021 and tag = 'question',days,null))) as act_days_2021_question#試卷作答記錄和題目練習記錄
from user_info
left join(
select distinct
uid,
date(start_time) as days,
'exam' as tag
from user_info
left join exam_record using(uid)
union
select distinct
uid,
date(submit_time) as days,
'question' as tag
from user_info
left join practice_record using(uid)
) main using(uid)
where level between 6 and 7
group by uid
order by act_month_total desc,act_days_2021 desc#按照總活躍月份數、2021年活躍天數降序排序