MySQL 合併查詢join 查詢出的不同列合併到一個表中

酸菜魚土豆大俠發表於2023-02-02
MySql

為了求解問題時思路清晰，建議先分列查詢，再將列合併到一個表中，這樣相當於將複雜問題拆解為簡單問題，一一解決。優點是避免所有問題混在一起，程式碼邏輯清晰，可遷移性強，下次遇到類似的查詢問題能快速求解，缺點是程式碼看起來不夠簡潔，存在程式碼冗餘的問題。

一、適用場景和方法

（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年活躍天數降序排序

擴充套件：

前往檢視MySQL 巢狀子查詢 with子句 from子查詢 in子查詢 join子查詢

相關文章