【SQL】SQL常見面試題總結（3）

目錄

  • 1、聚合函數
    • 1.1、SQL 類別高難度試卷得分的截斷平均值（較難）
    • 1.2、統計作答次數
    • 1.3、得分不小于平均分的最低分
  • 2、分組查詢
    • 2.1、平均活躍天數和月活人數
    • 2.2、月總刷題數和日均刷題數
    • 2.3、未完成試卷數大于 1 的有效用戶（較難）
  • 3、嵌套子查詢
    • 3.1、月均完成試卷數不小于 3 的用戶愛作答的類別（較難）
    • 3.2、試卷發布當天作答人數和平均分
    • 3.3、作答試卷得分大于過 80 的人的用戶等級分布
  • 4、合并查詢
    • 4.1、每個題目和每份試卷被作答的人數和次數
    • 4.2、分別滿足兩個活動的人
  • 5、連接查詢
    • 5.1、滿足條件的用戶的試卷完成數和題目練習數（困難）
    • 5.2、每個 6/7 級用戶活躍情況（困難）

1、聚合函數

1.1、SQL 類別高難度試卷得分的截斷平均值（較難）

描述：要查看大家在 SQL 類別中高難度試卷的得分情況。

請從exam_record數據表中計算所有用戶完成 SQL 類別高難度試卷得分的截斷平均值（去掉一個最大值和一個最小值后的平均值）。

示例數據：examination_info（exam_id 試卷 ID, tag 試卷類別, difficulty 試卷難度, duration 考試時長, release_time 發布時間）

idexam_idtagdifficultydurationrelease_time
19001SQLhard602020-01-01
29002算法medium802020-08-02

示例數據：exam_record（uid 用戶 ID, exam_id 試卷 ID, start_time 開始作答時間, submit_time 交卷時間, score 得分）

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:0180
2100190012021-05-02 10:01:012021-05-02 10:30:0181
3100190012021-06-02 19:01:012021-06-02 19:31:0184
4100190022021-09-05 19:01:012021-09-05 19:40:0189
5100190012021-09-02 12:01:01(NULL)(NULL)
6100190022021-09-01 12:01:01(NULL)(NULL)
7100290022021-02-02 19:01:012021-02-02 19:30:0187
8100290012021-05-05 18:01:012021-05-05 18:59:0290
9100390012021-09-07 12:01:012021-09-07 10:31:0150
10100490012021-09-06 10:01:01(NULL)(NULL)

根據輸入你的查詢結果如下：

tagdifficultyclip_avg_score
SQLhard81.7

從examination_info表可知，試卷 9001 為高難度 SQL 試卷，該試卷被作答的得分有[80,81,84,90,50]，去除最高分和最低分后為[80,81,84]，平均分為 81.6666667，保留一位小數后為 81.7

輸入描述：

輸入數據中至少有 3 個有效分數

思路一： 要找出高難度 sql 試卷，肯定需要聯 examination_info 這張表，然后找出高難度的課程，由 examination_info 得知，高難度 sql 的 exam_id 為 9001，那么等下就以 exam_id = 9001 作為條件去查詢；

先找出 9001 號考試 select * from exam_record where exam_id = 9001

然后，找出最高分 select max(score) 最高分 from exam_record where exam_id = 9001

接著，找出最低分 select min(score) 最低分 from exam_record where exam_id = 9001

在查詢出來的分數結果集當中，去掉最高分和最低分，最直觀能想到的就是 NOT IN 或者 用 NOT EXISTS 也行，這里以 NOT IN 來做

首先將主體寫出來select tag, difficulty, round(avg(score), 1) clip_avg_score from examination_info info INNER JOIN exam_record record

小 tips : MYSQL 的 ROUND() 函數 ,ROUND(X)返回參數 X 最近似的整數 ROUND(X,D)返回 X ,其值保留到小數點后 D 位,第 D 位的保留方式為四舍五入。

再將上面的 “碎片” 語句拼湊起來即可， 注意在 NOT IN 中兩個子查詢用 UNION ALL 來關聯，用 union 把 max 和 min 的結果集中在一行當中，這樣形成一列多行的效果。

答案一：

SELECT tag,difficulty,ROUND(AVG(score),1) clip_avg_score
FROM	examination_info info INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id AND record.exam_id = 9001AND record.score NOT IN (SELECT MAX(score)FROM exam_recordWHERE exam_id = 9001UNION ALLSELECT MIN(score)FROM exam_recordWHERE exam_id = 9001)

這是最直觀，也是最容易想到的解法，但是還有待改進，這算是投機取巧過關，其實嚴格按照題目要求應該這么寫：

SELECT info.tag, info.difficulty,ROUND(AVG(score),1) clip_avg_score
FROM examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id AND record.exam_id = (SELECT examination_info.exam_idFROM examination_infoWHERE tag = 'SQL'AND difficulty = 'hard') AND record.score NOT IN (SELECT MAX(score)FROM exam_recordWHERE exam_id = (SELECT examination_info.exam_idFROM examination_infoWHERE tag = 'SQL'AND difficulty = 'hard')UNION ALL SELECT MIN(score)FROM exam_recordWHERE exam_id = (SELECT examination_info.exam_idFROM examination_infoWHERE tag = 'SQL'AND difficulty = 'hard'))
GROUP BY info.tag, info.difficulty;

然而你會發現，重復的語句非常多，所以可以利用WITH來抽取公共部分

WITH 子句介紹：

WITH 子句，也稱為公共表表達式（Common Table Expression，CTE），是在 SQL 查詢中定義臨時表的方式。它可以讓我們在查詢中創建一個臨時命名的結果集，并且可以在同一查詢中引用該結果集。

基本用法：

WITH cte_name (column1, column2, ..., columnN) AS (-- 查詢體SELECT ...FROM ...WHERE ...
)
-- 主查詢
SELECT ...
FROM cte_name
WHERE ...

WITH 子句由以下幾個部分組成：

  • cte_name: 給臨時表起一個名稱，可以在主查詢中引用。
  • (column1, column2, ..., columnN): 可選，指定臨時表的列名。
  • AS: 必需，表示開始定義臨時表。
  • CTE 查詢體: 實際的查詢語句，用于定義臨時表中的數據。

WITH 子句的主要用途之一是增強查詢的可讀性和可維護性，尤其在涉及多個嵌套子查詢或需要重復使用相同的查詢邏輯時。通過將這些邏輯放在一個命名的臨時表中，我們可以更清晰地組織查詢，并消除重復代碼。

此外，WITH 子句還可以在復雜的查詢中實現遞歸查詢。遞歸查詢允許我們在單個查詢中執行對同一表的多次迭代，逐步構建結果集。這在處理層次結構數據、組織結構和樹狀結構等場景中非常有用。

小細節：MySQL 5.7 版本以及之前的版本不支持在 WITH 子句中直接使用別名。

下面是改進后的答案：

WITH t1 AS (SELECT record.*,info.tag,info.difficultyFROM exam_record recordINNER JOIN examination_info info ON record.exam_id = info.exam_idWHERE info.tag = 'SQL'AND info.difficulty = 'hard'
)
SELECT tag,difficulty,ROUND(AVG(score), 1) AS clip_avg_score
FROM t1
WHERE score NOT IN (SELECT MAX(score)FROM t1UNION ALLSELECT MIN(score)FROM t1
)
GROUP BY tag, difficulty;

思路二：

  • 篩選 SQL 高難度試卷：where tag="SQL" and difficulty="hard"
  • 計算截斷平均值：(和-最大值-最小值) / (總個數-2):
    • (sum(score) - max(score) - min(score)) / (count(score) - 2)
    • 有一個缺點就是，如果最大值和最小值有多個，這個方法就很難篩選出來, 但是題目中說了----->去掉一個最大值和一個最小值后的平均值, 所以這里可以用這個公式。

答案二：

SELECT info.tag,info.difficulty,ROUND((SUM(record.score)-MIN(record.score)-MAX(record.score))/(COUNT(record.score)-2),1) AS clip_avg_score
FROM examination_info info,exam_record record
WHERE info.exam_id = record.exam_idAND info.tag = 'SQL'AND info.difficulty = 'hard';

1.2、統計作答次數

有一個試卷作答記錄表 exam_record，請從中統計出總作答次數 total_pv、試卷已完成作答數 complete_pv、已完成的試卷數 complete_exam_cnt。

示例數據 exam_record 表（uid 用戶 ID, exam_id 試卷 ID, start_time 開始作答時間, submit_time 交卷時間, score 得分）：

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:0180
2100190012021-05-02 10:01:012021-05-02 10:30:0181
3100190012021-06-02 19:01:012021-06-02 19:31:0184
4100190022021-09-05 19:01:012021-09-05 19:40:0189
5100190012021-09-02 12:01:01(NULL)(NULL)
6100190022021-09-01 12:01:01(NULL)(NULL)
7100290022021-02-02 19:01:012021-02-02 19:30:0187
8100290012021-05-05 18:01:012021-05-05 18:59:0290
9100390012021-09-07 12:01:012021-09-07 10:31:0150
10100490012021-09-06 10:01:01(NULL)(NULL)

示例輸出：

total_pvcomplete_pvcomplete_exam_cnt
1072

解釋：表示截止當前，有 11 次試卷作答記錄，已完成的作答次數為 7 次（中途退出的為未完成狀態，其交卷時間和份數為 NULL），已完成的試卷有 9001 和 9002 兩份。

思路： 這題一看到統計次數，肯定第一時間就要想到用COUNT這個函數來解決，問題是要統計不同的記錄，該怎么來寫？使用子查詢就能解決這個題目(這題用 case when 也能寫出來，解法類似，邏輯不同而已)；首先在做這個題之前，讓我們先來了解一下COUNT的基本用法；

COUNT() 函數的基本語法如下所示：

COUNT(expression)

其中，expression 可以是列名、表達式、常量或通配符。下面是一些常見的用法示例：

  1. 計算表中所有行的數量：
SELECT COUNT(*) FROM table_name;
  1. 計算特定列非空（不為 NULL）值的數量：
SELECT COUNT(column_name) FROM table_name;
  1. 計算滿足條件的行數：
SELECT COUNT(*) FROM table_name WHERE condition;
  1. 結合 GROUP BY 使用，計算分組后每個組的行數：
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
  1. 計算不同列組合的唯一組合數：
SELECT COUNT(DISTINCT column_name1, column_name2) FROM table_name;

在使用COUNT()函數時，如果不指定任何參數或者使用 COUNT(*)，將會計算所有行的數量。而如果使用列名，則只會計算該列非空值的數量。

另外，COUNT() 函數的結果是一個整數值。即使結果是零，也不會返回 NULL，這點需要謹記。

答案：

SELECT COUNT(*) total_pv,(SELECT COUNT(*) FROM exam_record WHERE submit_time IS NOT NULL) complete_pv,(SELECT COUNT(DISTINCT exam_id, score IS NOT NULL OR NULL) FROM exam_record) complete_exam_cnt
FROM exam_record

這里著重說一下COUNT( DISTINCT exam_id, score IS NOT NULL OR NULL )這一句，判斷 score 是否為 null ，如果是即為真，如果不是返回 null；注意這里如果不加 or null 在不是 null 的情況下只會返回 false 也就是返回 0；

COUNT本身是不可以對多列求行數的，distinct的加入是的多列成為一個整體，可以求出現的行數了;count distinct在計算時只返回非 null 的行, 這個也要注意；

另外通過本題 get 到了------>count 加條件常用句式count( 列判斷 or null)

1.3、得分不小于平均分的最低分

描述： 請從試卷作答記錄表中找到 SQL 試卷得分不小于該類試卷平均得分的用戶最低得分。

示例數據 exam_record 表（uid 用戶 ID, exam_id 試卷 ID, start_time 開始作答時間, submit_time 交卷時間, score 得分）：

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:0180
2100290012021-09-05 19:01:012021-09-05 19:40:0189
3100290022021-09-02 12:01:01(NULL)(NULL)
4100290032021-09-01 12:01:01(NULL)(NULL)
5100290012021-02-02 19:01:012021-02-02 19:30:0187
6100290022021-05-05 18:01:012021-05-05 18:59:0290
7100390022021-02-06 12:01:01(NULL)(NULL)
8100390032021-09-07 10:01:012021-09-07 10:31:0186
9100490032021-09-06 12:01:01(NULL)(NULL)

examination_info 表（exam_id 試卷 ID, tag 試卷類別, difficulty 試卷難度, duration 考試時長, release_time 發布時間）

idexam_idtagdifficultydurationrelease_time
19001SQLhard602020-01-01
29002SQLeasy602020-02-01
39003算法medium802020-08-02

示例輸出數據：

min_score_over_avg
87

解釋：試卷 9001 和 9002 為 SQL 類別，作答這兩份試卷的得分有[80,89,87,90]，平均分為 86.5，不小于平均分的最小分數為 87

思路：這類題目第一眼看確實很復雜， 因為不知道從哪入手，但是當我們仔細讀題審題后，要學會抓住題干中的關鍵信息。以本題為例：請從試卷作答記錄表中找到SQL試卷得分不小于該類試卷平均得分的用戶最低得分。你能一眼從中提取哪些有效信息來作為解題思路？

第一條：找到SQL試卷得分

第二條：該類試卷平均得分

第三條：該類試卷的用戶最低得分

然后中間的 “橋梁” 就是不小于

將條件拆分后，先逐步完成

-- 找出tag為‘SQL’的得分   【80, 89,87,90】
SELECT record.score
FROM exam_record record,examination_info info
WHERE record.exam_id = info.exam_id AND info.tag = 'SQL' AND record.score IS NOT NULL-- 再算出這一組的平均得分
SELECT ROUND(AVG(score),1) 
FROM examination_info info 
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
AND tag= 'SQL'

然后再找出該類試卷的最低得分，接著將結果集【80, 89,87,90】 去和平均分數作比較，方可得出最終答案。

答案：

SELECT MIN(score) AS min_score_over_avg
FROM	examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
AND info.tag = 'SQL'
AND score > (SELECT ROUND(AVG(score),1) from  examination_info info INNER JOIN exam_record recordwhere info.exam_id = record.exam_idand tag= 'SQL'
)

在這里插入圖片描述

其實這類題目給出的要求看似很 “繞”，但其實仔細梳理一遍，將大條件拆分成小條件，逐個拆分完以后，最后將所有條件拼湊起來。反正只要記住：抓主干，理分支，問題便迎刃而解。

2、分組查詢

2.1、平均活躍天數和月活人數

描述：用戶在試卷作答區作答記錄存儲在表exam_record中，內容如下：

exam_record 表（uid 用戶 ID, exam_id 試卷 ID, start_time 開始作答時間, submit_time 交卷時間,score 得分）

iduidexam_idstart_timesubmit_timescore
1100190012021-07-02 09:01:012021-07-02 09:21:0180
2100290012021-09-05 19:01:012021-09-05 19:40:0181
3100290022021-09-02 12:01:01(NULL)(NULL)
4100290032021-09-01 12:01:01(NULL)(NULL)
5100290012021-07-02 19:01:012021-07-02 19:30:0182
6100290022021-07-05 18:01:012021-07-05 18:59:0290
7100390022021-07-06 12:01:01(NULL)(NULL)
8100390032021-09-07 10:01:012021-09-07 10:31:0186
9100490032021-09-06 12:01:01(NULL)(NULL)
10100290032021-09-01 12:01:012021-09-01 12:31:0181
11100590012021-09-01 12:01:012021-09-01 12:31:0188
12100690022021-09-02 12:11:012021-09-02 12:31:0189
13100790022020-09-02 12:11:012020-09-02 12:31:0189

請計算 2021 年每個月里試卷作答區用戶平均月活躍天數 avg_active_days 和月度活躍人數 mau，上面數據的示例輸出如下：

monthavg_active_daysmau
2021071.502
2021091.254

解釋：2021 年 7 月有 2 人活躍，共活躍了 3 天（1001 活躍 1 天，1002 活躍 2 天），平均活躍天數 1.5；2021 年 9 月有 4 人活躍，共活躍了 5 天，平均活躍天數 1.25，結果保留 2 位小數。

注：此處活躍指有交卷行為。

思路：讀完題先注意高亮部分；一般求天數和月活躍人數馬上就要想到相關的日期函數；這一題我們同樣來進行拆分，把問題細化再解決；首先求活躍人數，肯定要用到COUNT()，那這里首先就有一個坑，不知道大家注意了沒有？用戶 1002 在 9 月份做了兩種不同的試卷，所以這里要注意去重，不然在統計的時候，活躍人數是錯的；第二個就是要知道日期的格式化，如上表，題目要求以202107這種日期格式展現，要用到DATE_FORMAT來進行格式化。

基本用法：

DATE_FORMAT(date_value, format)

  • date_value 參數是待格式化的日期或時間值。
  • format 參數是指定的日期或時間格式（這個和 Java 里面的日期格式一樣）。

答案：

SELECT DATE_FORMAT(submit_time,'%Y%m') month,COUNT(DISTINCT uid,DATE_FORMAT(submit_time, '%Y%m%d'))/COUNT(DISTINCT UID) avg_active_days,COUNT(DISTINCT uid) mau
FROM exam_record
WHERE YEAR(submit_time) = '2021'
GROUP BY month

在這里插入圖片描述
這里多說一句, 使用COUNT(DISTINCT uid, DATE_FORMAT(submit_time, '%Y%m%d') 可以統計在 uid 列和 submit_time 列按照年份、月份和日期進行格式化后的組合值的數量。

2.2、月總刷題數和日均刷題數

描述：現有一張題目練習記錄表 practice_record，示例內容如下：

iduidquestion_idsubmit_timescore
1100180012021-08-02 11:41:0160
2100280012021-09-02 19:30:0150
3100280012021-09-02 19:20:0170
4100280022021-09-02 19:38:0170
5100380022021-08-01 19:38:0180

請從中統計出 2021 年每個月里用戶的月總刷題數 month_q_cnt 和日均刷題數 avg_day_q_cnt（按月份升序排序）以及該年的總體情況，示例數據輸出如下：

submit_monthmonth_q_cntavg_day_q_cnt
20210820.065
20210930.100
2021 匯總50.161

解釋：2021 年 8 月共有 2 次刷題記錄，日均刷題數為 2/31=0.065（保留 3 位小數）；2021 年 9 月共有 3 次刷題記錄，日均刷題數為 3/30=0.100；2021 年共有 5 次刷題記錄（年度匯總平均無實際意義，這里我們按照 31 天來算 5/31=0.161）

如果您采用最新的 Mysql 版本，如果您運行結果出現錯誤：ONLY_FULL_GROUP_BY，意思是：對于 GROUP BY
聚合操作，如果在 SELECT 中的列，沒有在 GROUP BY 中出現，那么這個 SQL 是不合法的，因為列不在 GROUP BY
從句中，也就是說查出來的列必須在 group by 后面出現否則就會報錯，或者這個字段出現在聚合函數里面。

思路：

看到實例數據就要馬上聯想到相關的函數，比如submit_month就要用到DATE_FORMAT來格式化日期。然后查出每月的刷題數量。

每月的刷題數量

SELECT MONTH ( submit_time ), COUNT( question_id )
FROMpractice_record
GROUP BYMONTH (submit_time)

在這里插入圖片描述

接著第三列這里要用到DAY(LAST_DAY(date_value))函數來查找給定日期的月份中的天數。

示例代碼如下：

SELECT DAY(LAST_DAY('2023-07-08')) AS days_in_month;
-- 輸出：31SELECT DAY(LAST_DAY('2023-02-01')) AS days_in_month;
-- 輸出：28 (閏年中的二月份)SELECT DAY(LAST_DAY(NOW())) AS days_in_current_month;
-- 輸出：31 （當前月份的天數）

使用 LAST_DAY() 函數獲取給定日期的當月最后一天，然后使用 DAY() 函數提取該日期的天數。這樣就能獲得指定月份的天數。

需要注意的是，LAST_DAY() 函數返回的是日期值，而 DAY() 函數用于提取日期值中的天數部分。

有了上述的分析之后，即可馬上寫出答案，這題復雜就復雜在處理日期上，其中的邏輯并不難。

答案：

SELECT DATE_FORMAT(submit_time, '%Y%m') submit_month,count(question_id) month_q_cnt,ROUND(COUNT(question_id) / DAY (LAST_DAY(submit_time)), 3) avg_day_q_cnt
FROM practice_record
WHERE DATE_FORMAT(submit_time, '%Y') = '2021'
GROUP BY submit_month
UNION ALL
SELECT '2021匯總' AS submit_month,count(question_id) month_q_cnt,ROUND(COUNT(question_id) / 31, 3) avg_day_q_cnt
FROM practice_record
WHERE DATE_FORMAT(submit_time, '%Y') = '2021'
ORDER BY submit_month

在實例數據輸出中因為最后一行需要得出匯總數據，所以這里要 UNION ALL加到結果集中；別忘了最后要排序！

2.3、未完成試卷數大于 1 的有效用戶（較難）

描述：現有試卷作答記錄表 exam_record（uid 用戶 ID,exam_id試卷 ID,start_time開始作答時間, submit_time 交卷時間, score 得分），示例數據如下：

iduidexam_idstart_timesubmit_timescore
1100190012021-07-02 09:01:012021-07-02 09:21:0180
2100290012021-09-05 19:01:012021-09-05 19:40:0181
3100290022021-09-02 12:01:01(NULL)(NULL)
4100290032021-09-01 12:01:01(NULL)(NULL)
5100290012021-07-02 19:01:012021-07-02 19:30:0182
6100290022021-07-05 18:01:012021-07-05 18:59:0290
7100390022021-07-06 12:01:01(NULL)(NULL)
8100390032021-09-07 10:01:012021-09-07 10:31:0186
9100490032021-09-06 12:01:01(NULL)(NULL)
10100290032021-09-01 12:01:012021-09-01 12:31:0181
11100590012021-09-01 12:01:012021-09-01 12:31:0188
12100690022021-09-02 12:11:012021-09-02 12:31:0189
13100790022020-09-02 12:11:012020-09-02 12:31:0189

還有一張試卷信息表examination_info 表（exam_id 試卷 ID, tag 試卷類別, difficulty 試卷難度, duration 考試時長, release_time 發布時間）
示例數據如下：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602020-01-01
29002SQLeasy602020-02-01
39003算法medium802020-08-02

請統計 2021 年每個未完成試卷作答數大于 1 的有效用戶的數據（有效用戶指完成試卷作答數至少為 1 且未完成數小于 5），輸出用戶 ID、未完成試卷作答數、完成試卷作答數、作答過的試卷 tag 集合，按未完成試卷數量由多到少排序。示例數據的輸出結果如下：

uidincomplete_cntcomplete_cntdetail
1002242021-09-01:算法;2021-07-02:SQL;2021-09-02:SQL;2021-09-05:SQL;2021-07-05:SQL

解釋：2021 年的作答記錄中，除了 1004，其他用戶均滿足有效用戶定義，但只有 1002 未完成試卷數大于 1，因此只輸出 1002，detail 中是 1002 作答過的試卷{日期:tag}集合，日期和 tag 間用 : 連接，多元素間用 ; 連接。

思路：

仔細讀題后，分析出：首先要聯表，因為后面要輸出tag；

篩選出 2021 年的數據

SELECT *
FROM exam_record er
LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id
WHERE YEAR(er.start_time) = '2021'

根據 uid 進行分組，然后對每個用戶進行條件進行判斷，題目中要求完成試卷數至少為1,未完成試卷數要大于1，小于5

那么等會兒寫 sql 的時候條件應該是：未完成 > 1 and 已完成 >=1 and 未完成 < 5

因為最后要用到字符串的拼接，而且還要組合拼接，這個可以用GROUP_CONCAT函數，下面簡單介紹一下該函數的用法：

基本格式：

GROUP_CONCAT([DISTINCT] expr [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [, ...]]             [SEPARATOR sep])
  • expr：要連接的列或表達式。
  • DISTINCT：可選參數，用于去重。當指定了 DISTINCT，相同的值只會出現一次。
  • ORDER BY：可選參數，用于排序連接后的值。可以選擇升序 (ASC) 或降序 (DESC) 排序。
  • SEPARATOR sep：可選參數，用于設置連接后的值的分隔符。（本題要用這個參數設置 ; 號 ）

GROUP_CONCAT() 函數常用于 GROUP BY 子句中，將一組行的值連接為一個字符串，并在結果集中以聚合的形式返回。

答案：

SELECT a.uid,SUM(CASE WHEN a.submit_time IS NULL THEN 1END) AS incomplete_cnt,SUM(CASE WHEN a.submit_time IS NOT NULL THEN 1END) AS complete_cnt,GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(a.start_time,'%Y-%m-%d'),':',b.tag)ORDER BY start_time SEPARATOR ';') AS detail
FROM exam_record a
LEFT JOIN examination_info b ON a.exam_id = b.exam_id
WHERE YEAR (a.start_time)= 2021
GROUP BY a.uid
HAVING incomplete_cnt > 1
AND complete_cnt >=1
AND incomplete_cnt < 5
ORDER BY incomplete_cnt DESC

在這里插入圖片描述

  • SUM(CASE WHEN a.submit_time IS NULL THEN 1 END) 統計了每個用戶未完成的記錄數量。
  • SUM(CASE WHEN a.submit_time IS NOT NULL THEN 1 END) 統計了每個用戶已完成的記錄數量。
  • GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(a.start_time, '%Y-%m-%d'), ':', b.tag) ORDER BY a.start_time SEPARATOR ';') 將每個用戶的考試日期和標簽以逗號分隔的形式連接成一個字符串，并按考試開始時間進行排序。

3、嵌套子查詢

3.1、月均完成試卷數不小于 3 的用戶愛作答的類別（較難）

描述：現有試卷作答記錄表 exam_record（uid：用戶 ID, exam_id：試卷 ID, start_time：開始作答時間, submit_time：交卷時間，沒提交的話為 NULL, score：得分），示例數據如下：

iduidexam_idstart_timesubmit_timescore
1100190012021-07-02 09:01:01(NULL)(NULL)
2100290032021-09-01 12:01:012021-09-01 12:21:0160
3100290022021-09-02 12:01:012021-09-02 12:31:0170
4100290012021-09-05 19:01:012021-09-05 19:40:0181
5100290022021-07-06 12:01:01(NULL)(NULL)
6100390032021-09-07 10:01:012021-09-07 10:31:0186
7100390032021-09-08 12:01:012021-09-08 12:11:0140
8100390012021-09-08 13:01:01(NULL)(NULL)
9100390022021-09-08 14:01:01(NULL)(NULL)
10100390032021-09-08 15:01:01(NULL)(NULL)
11100590012021-09-01 12:01:012021-09-01 12:31:0188
12100590022021-09-01 12:01:012021-09-01 12:31:0188
13100590022021-09-02 12:11:012021-09-02 12:31:0189

試卷信息表 examination_info（exam_id：試卷 ID, tag：試卷類別, difficulty：試卷難度, duration：考試時長, release_time：發布時間），示例數據如下：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602020-01-01 10:00:00
29002C++easy602020-02-01 10:00:00
39003算法medium802020-08-02 10:00:00

請從表中統計出 “當月均完成試卷數”不小于 3 的用戶們愛作答的類別及作答次數，按次數降序輸出，示例輸出如下：

tagtag_cnt
C++4
SQL2
算法1

解釋：用戶 1002 和 1005 在 2021 年 09 月的完成試卷數目均為 3，其他用戶均小于 3；然后用戶 1002 和 1005 作答過的試卷 tag 分布結果按作答次數降序排序依次為 C++、SQL、算法。

思路：這題考察聯合子查詢，重點在于月均回答>=3, 但是個人認為這里沒有表述清楚，應該直接說查 9 月的就容易理解多了；這里不是每個月都要>=3 或者是所有答題次數/答題月份。不要理解錯誤了。

先查詢出哪些用戶月均答題大于三次

SELECT uid
FROM exam_record
GROUP BY uid,MONTH(start_time)
HAVING COUNT(submit_time) >= 3

在這里插入圖片描述
有了這一步之后再進行深入，只要能理解上一步(我的意思是不被題目中的月均所困擾)，然后再套一個子查詢，查哪些用戶包含其中，然后查出題目中所需的列即可。記得排序！！

SELECT tag,COUNT(start_time) AS tag_cnt
FROM exam_record record
INNER JOIN examination_info info ON record.exam_id = info.exam_id
WHERE uid IN (SELECT uidFROM exam_recordGROUP BY uid,MONTH(start_time)HAVING COUNT(submit_time) >= 3)
GROUP BY tag
ORDER BY tag_cnt DESC

在這里插入圖片描述

3.2、試卷發布當天作答人數和平均分

描述：現有用戶信息表 user_info（uid 用戶 ID，nick_name 昵稱, achievement 成就值,level等級, job 職業方向, register_time 注冊時間），示例數據如下：

iduidnick_nameachievementleveljobregister_time
11001機器人 1 號31007算法2020-01-01 10:00:00
21002機器人 2 號21006算法2020-01-01 10:00:00
31003機器人 3 號15005算法2020-01-01 10:00:00
41004機器人 4 號11004算法2020-01-01 10:00:00
51005機器人 5 號16006C++2020-01-01 10:00:00
61006機器人 6 號30006C++2020-01-01 10:00:00

釋義：用戶 1001 昵稱為機器人 1 號，成就值為 3100，用戶等級是 7 級，職業方向為算法，注冊時間 2020-01-01 10:00:00

試卷信息表 examination_info（exam_id：試卷 ID, tag：試卷類別, difficulty：試卷難度, duration：考試時長, release_time：發布時間），示例數據如下：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-09-01 06:00:00
29002C++easy602020-02-01 10:00:00
39003算法medium802020-08-02 10:00:00

試卷作答記錄表 exam_record（uid 用戶 ID,exam_id 試卷 ID, start_time 開始作答時間, submit_time 交卷時間, score 得分） 示例數據如下：

iduidexam_idstart_timesubmit_timescore
1100190012021-09-01 09:01:012021-09-01 09:41:0170
2100290032021-09-01 12:01:012021-09-01 12:21:0160
3100290022021-09-02 12:01:012021-09-02 12:31:0170
4100290012021-09-01 19:01:012021-09-01 19:40:0180
5100290032021-08-01 12:01:012021-08-01 12:21:0160
6100290022021-08-02 12:01:012021-08-02 12:31:0170
7100290012021-09-01 19:01:012021-09-01 19:40:0185
8100290022021-07-06 12:01:01(NULL)(NULL)
9100390022021-09-07 10:01:012021-09-07 10:31:0186
10100390032021-09-08 12:01:012021-09-08 12:11:0140
11100390032021-09-01 13:01:012021-09-01 13:41:0170
12100390012021-09-08 14:01:01(NULL)(NULL)
13100390022021-09-08 15:01:01(NULL)(NULL)
14100590012021-09-01 12:01:012021-09-01 12:31:0190
15100590022021-09-01 12:01:012021-09-01 12:31:0188
16100590022021-09-02 12:11:012021-09-02 12:31:0189

請計算每張 SQL 類別試卷發布后，當天 5 級以上的用戶作答的人數 uv 和平均分 avg_score，按人數降序，相同人數的按平均分升序，示例數據結果輸出如下：

exam_iduvavg_score
9001381.3

解釋：只有一張 SQL 類別的試卷，試卷 ID 為 9001，發布當天（2021-09-01）有 1001、1002、1003、1005 作答過，但是 1003 是 5 級用戶，其他 3 位為 5 級以上，他們三的得分有[70,80,85,90]，平均分為 81.3（保留 1 位小數）。

思路：這題看似很復雜，但是先逐步將“外邊”條件拆分，然后合攏到一起，答案就出來，多表查詢反正記住：由外向里，抽絲剝繭。

先把三種表連起來，同時給定一些條件，比如題目中要求等級> 5的用戶，那么可以先查出來

SELECT DISTINCT ui.uid
FROM examination_info ei
INNER JOIN exam_record er
INNER JOIN user_info ui
WHERE ei.exam_id = er.exam_idAND er.uid = ui.uidAND ui.`level` > 5

接著注意題目中要求：每張sql類別試卷發布后，當天作答用戶，注意其中的當天，那我們馬上就要想到要用到時間的比較。

對試卷發布日期和開始考試日期進行比較：DATE(ei.release_time) = DATE(er.start_time)；不用擔心submit_time 為 null 的問題，后續在 where 中會給過濾掉。

答案：

SELECT er.exam_id AS exam_id,COUNT(DISTINCT ui.uid) AS uv,ROUND(SUM(er.score)/COUNT(ui.uid),1) AS avg_score
FROM examination_info ei
INNER JOIN exam_record er
INNER JOIN user_info ui
WHERE ei.exam_id = er.exam_idAND er.uid = ui.uidAND DATE(ei.release_time) = DATE(er.start_time)AND submit_time IS NOT NULLAND ei.tag = 'SQL'AND ui.`level` > 5
GROUP BY er.exam_id
ORDER BY uv DESC,avg_score ASC

在這里插入圖片描述
注意最后的分組排序！先按人數排，若一致，按平均分排。

3.3、作答試卷得分大于過 80 的人的用戶等級分布

描述：

現有用戶信息表 user_info（uid 用戶 ID，nick_name 昵稱, achievement 成就值,level等級, job 職業方向, register_time 注冊時間），示例數據如下：

iduidnick_nameachievementleveljobregister_time
11001機器人 1 號31007算法2020-01-01 10:00:00
21002機器人 2 號21006算法2020-01-01 10:00:00
31003機器人 3 號15005算法2020-01-01 10:00:00
41004機器人 4 號11004算法2020-01-01 10:00:00
51005機器人 5 號16006C++2020-01-01 10:00:00
61006機器人 6 號30006C++2020-01-01 10:00:00

試卷信息表 examination_info（exam_id：試卷 ID, tag：試卷類別, difficulty：試卷難度, duration：考試時長, release_time：發布時間），示例數據如下：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-09-01 06:00:00
29002C++easy602020-02-01 10:00:00
39003算法medium802020-08-02 10:00:00

試卷作答記錄表 exam_record（uid 用戶 ID,exam_id 試卷 ID, start_time 開始作答時間, submit_time 交卷時間, score 得分） 示例數據如下：

iduidexam_idstart_timesubmit_timescore
1100190012021-09-01 09:01:012021-09-01 09:41:0179
2100290032021-09-01 12:01:012021-09-01 12:21:0160
3100290022021-09-02 12:01:012021-09-02 12:31:0170
4100290012021-09-01 19:01:012021-09-01 19:40:0180
5100290032021-08-01 12:01:012021-08-01 12:21:0160
6100290022021-08-02 12:01:012021-08-02 12:31:0170
7100290012021-09-01 19:01:012021-09-01 19:40:0185
8100290022021-07-06 12:01:01(NULL)(NULL)
9100390012021-09-07 10:01:012021-09-07 10:31:0186
10100390032021-09-08 12:01:012021-09-08 12:11:0140
11100390032021-09-01 13:01:012021-09-01 13:41:0181
12100390012021-09-08 14:01:01(NULL)(NULL)
13100390022021-09-08 15:01:01(NULL)(NULL)
14100590012021-09-01 12:01:012021-09-01 12:31:0190
15100590022021-09-01 12:01:012021-09-01 12:31:0188
16100590022021-09-02 12:11:012021-09-02 12:31:0189

統計作答 SQL 類別的試卷得分大于過 80 的人的用戶等級分布，按數量降序排序（保證數量都不同）。示例數據結果輸出如下：

levellevel_cnt
62
51

解釋：9001 為 SQL 類試卷，作答該試卷大于 80 分的人有 1002、1003、1005 共 3 人，6 級兩人，5 級一人。

思路：這題和上一題都是一樣的數據，只是查詢條件改變了而已，上一題理解了，這題分分鐘做出來。

答案：

SELECT ui.`level` AS level,COUNT(DISTINCT ui.uid) AS level_cnt
FROM examination_info ei
INNER JOIN exam_record er
INNER JOIN user_info ui
WHERE ei.exam_id = er.exam_idAND er.uid = ui.uidAND ei.tag = 'SQL'AND er.score > 80
GROUP BY level
ORDER BY level_cnt DESC

在這里插入圖片描述

4、合并查詢

4.1、每個題目和每份試卷被作答的人數和次數

描述：

現有試卷作答記錄表 exam_record（uid 用戶 ID,exam_id 試卷 ID, start_time 開始作答時間, submit_time 交卷時間, score 得分） 示例數據如下：

iduidexam_idstart_timesubmit_timescore
1100190012021-09-01 09:01:012021-09-01 09:41:0181
2100290022021-09-01 12:01:012021-09-01 12:31:0170
3100290012021-09-01 19:01:012021-09-01 19:40:0180
4100290022021-09-01 12:01:012021-09-01 12:31:0170
5100490012021-09-01 19:01:012021-09-01 19:40:0185
6100290022021-09-01 12:01:01(NULL)(NULL)

題目練習表 practice_record（uid 用戶 ID, question_id 題目 ID, submit_time 提交時間, score 得分）：

iduidquestion_idsubmit_timescore
1100180012021-08-02 11:41:0160
2100280012021-09-02 19:30:0150
3100280012021-09-02 19:20:0170
4100280022021-09-02 19:38:0170
5100380012021-08-02 19:38:0170
6100380012021-08-02 19:48:0190
7100380022021-08-01 19:38:0180

請統計每個題目和每份試卷被作答的人數和次數，分別按照"試卷"和"題目"的 uv & pv 降序顯示，示例數據結果輸出如下：

tiduvpv
900133
900213
800135
800222

解釋：“試卷”有 3 人共練習 3 次試卷 9001，1 人作答 3 次 9002；“刷題”有 3 人刷 5 次 8001，有 2 人刷 2 次 8002

思路：這題的難點和易錯點在于UNION和ORDER BY 同時使用的問題

有以下幾種情況：使用union和多個order by不加括號，報錯！

order by在union連接的子句中不起作用；

比如不加括號：

SELECT exam_id AS tid,COUNT(DISTINCT UID) AS uv,COUNT(UID) AS pv
FROM exam_record
GROUP BY exam_id
ORDER BY uv DESC,pv DESC
UNION
SELECT question_id AS tid,COUNT(DISTINCT UID) AS uv,COUNT(UID) AS pv
FROM practice_record
GROUP BY question_id
ORDER BY uv DESC,pv DESC

直接報語法錯誤，如果沒有括號，只能有一個order by

還有一種order by不起作用的情況，但是能在子句的子句中起作用，這里的解決方案就是在外面再套一層查詢。

答案：

SELECT *
FROM(SELECT exam_id AS tid,COUNT(DISTINCT exam_record.uid) uv,COUNT(*) pvFROM exam_recordGROUP BY exam_idORDER BY uv DESC, pv DESC) t1
UNION
SELECT *
FROM(SELECT question_id AS tid,COUNT(DISTINCT practice_record.uid) uv,COUNT(*) pvFROM practice_recordGROUP BY question_idORDER BY uv DESC, pv DESC) t2;

在這里插入圖片描述

4.2、分別滿足兩個活動的人

描述： 為了促進更多用戶在平臺學習和刷題進步，我們會經常給一些既活躍又表現不錯的用戶發放福利。假使以前我們有兩撥運營活動，分別給每次試卷得分都能到 85 分的人（activity1）、至少有一次用了一半時間就完成高難度試卷且分數大于 80 的人（activity2）發了福利券。

現在，需要你一次性將這兩個活動滿足的人篩選出來，交給運營同學。請寫出一個 SQL 實現：輸出 2021 年里，所有每次試卷得分都能到 85 分的人以及至少有一次用了一半時間就完成高難度試卷且分數大于 80 的人的 id 和活動號，按用戶 ID 排序輸出。

現有試卷信息表 examination_info（exam_id：試卷 ID, tag：試卷類別, difficulty：試卷難度, duration：考試時長, release_time：發布時間），示例數據如下：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-09-01 06:00:00
29002C++easy602020-02-01 10:00:00
39003算法medium802020-08-02 10:00:00

現有試卷作答記錄表 exam_record（uid 用戶 ID,exam_id 試卷 ID, start_time 開始作答時間, submit_time 交卷時間, score 得分） 示例數據如下：

iduidexam_idstart_timesubmit_timescore
1100190012021-09-01 09:01:012021-09-01 09:31:0081
2100290022021-09-01 12:01:012021-09-01 12:31:0170
3100390012021-09-01 19:01:012021-09-01 19:40:0186
4100390022021-09-01 12:01:012021-09-01 12:31:0189
5100490012021-09-01 19:01:012021-09-01 19:30:0185

示例數據輸出結果：

uidactivity
1001activity2
1003activity1
1004activity1
1004activity2

解釋：用戶 1001 最小分數 81 不滿足活動 1，但 29 分 59 秒完成了 60 分鐘長的試卷得分 81，滿足活動 2；1003 最小分數 86 滿足活動 1，完成時長都大于試卷時長的一半，不滿足活動 2；用戶 1004 剛好用了一半時間（30 分鐘整）完成了試卷得分 85，滿足活動 1 和活動 2。

思路： 這一題需要涉及到時間的減法，需要用到 TIMESTAMPDIFF() 函數計算兩個時間戳之間的分鐘差值。

下面我們來看一下基本用法

示例：

TIMESTAMPDIFF(MINUTE, start_time, end_time)

TIMESTAMPDIFF() 函數的第一個參數是時間單位，這里我們選擇 MINUTE 表示返回分鐘差值。第二個參數是較早的時間戳，第三個參數是較晚的時間戳。函數會返回它們之間的分鐘差值

了解了這個函數的用法之后，我們再回過頭來看activity1的要求，求分數大于 85 即可，那我們還是先把這個寫出來，后續思路就會清晰很多

SELECT DISTINCT uid
FROM exam_record
WHERE score >= 85AND YEAR(start_time) = '2021'

在這里插入圖片描述
根據條件 2，接著寫出在一半時間內完成高難度試卷且分數大于80的人

SELECT uid
FROM exam_record er
INNER JOIN examination_info ei
WHERE er. exam_id = ei.exam_id
AND score >= 80
AND (TIMESTAMPDIFF(MINUTE,start_time,submit_time)) < (ei.duration/2)
AND ei.difficulty = 'hard'

在這里插入圖片描述
然后再把兩者UNION 起來即可。（這里特別要注意括號問題和order by位置，具體用法在上一篇中已提及）

SELECT DISTINCT uid,'activity1' AS activity
FROM exam_record
WHERE score >= 85AND YEAR(start_time) = '2021'
UNION
SELECT uid,'activity2' AS activity
FROM exam_record er
INNER JOIN examination_info ei
WHERE er. exam_id = ei.exam_id
AND score >= 80
AND (TIMESTAMPDIFF(MINUTE,start_time,submit_time)) < (ei.duration/2)
AND ei.difficulty = 'hard'
ORDER BY uid

在這里插入圖片描述

5、連接查詢

5.1、滿足條件的用戶的試卷完成數和題目練習數（困難）

描述：

現有用戶信息表 user_info（uid 用戶 ID，nick_name 昵稱, achievement 成就值,level等級, job 職業方向, register_time 注冊時間），示例數據如下：

iduidnick_nameachievementleveljobregister_time
11001機器人 1 號31007算法2020-01-01 10:00:00
21002機器人 2 號23007算法2020-01-01 10:00:00
31003機器人 3 號25007算法2020-01-01 10:00:00
41004機器人 4 號12005算法2020-01-01 10:00:00
51005機器人 5 號16006C++2020-01-01 10:00:00
61006機器人 6 號20006C++2020-01-01 10:00:00

現有試卷信息表 examination_info（exam_id：試卷 ID, tag：試卷類別, difficulty：試卷難度, duration：考試時長, release_time：發布時間），示例數據如下：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-09-01 06:00:00
29002C++hard602021-09-01 06:00:00
39003算法medium802021-09-01 10:00:00

現有試卷作答記錄表 exam_record（uid 用戶 ID,exam_id 試卷 ID, start_time 開始作答時間, submit_time 交卷時間, score 得分） 示例數據如下：

iduidexam_idstart_timesubmit_timescore
1100190012021-09-01 09:01:012021-09-01 09:31:0081
2100290022021-09-01 12:01:012021-09-01 12:31:0181
3100390012021-09-01 19:01:012021-09-01 19:40:0186
4100390022021-09-01 12:01:012021-09-01 12:31:5189
5100490012021-09-01 19:01:012021-09-01 19:30:0185
6100590022021-09-01 12:01:012021-09-01 12:31:0285
7100690032021-09-07 10:01:012021-09-07 10:21:0184
8100690012021-09-07 10:01:012021-09-07 10:21:0180

題目練習表 practice_record（uid 用戶 ID, question_id 題目 ID, submit_time 提交時間, score 得分）：

iduidquestion_idsubmit_timescore
1100180012021-08-02 11:41:0160
2100280012021-09-02 19:30:0150
3100280012021-09-02 19:20:0170
4100280022021-09-02 19:38:0170
5100480012021-08-02 19:38:0170
6100480022021-08-02 19:48:0190
7100180022021-08-02 19:38:0170
8100480022021-08-02 19:48:0190
9100480022021-08-02 19:58:0194
10100480032021-08-02 19:38:0170
11100480032021-08-02 19:48:0190
12100480032021-08-01 19:38:0180

請你找到高難度 SQL 試卷得分平均值大于 80 并且是 7 級的紅名大佬，統計他們的 2021 年試卷總完成次數和題目總練習次數，只保留 2021 年有試卷完成記錄的用戶。結果按試卷完成數升序，按題目練習數降序。

示例數據輸出如下：

uidexam_cntquestion_cnt
100112
100320

解釋：用戶 1001、1003、1004、1006 滿足高難度 SQL 試卷得分平均值大于 80，但只有 1001、1003 是 7 級紅名大佬；1001 完成了 1 次試卷 1001，練習了 2 次題目；1003 完成了 2 次試卷 9001、9002，未練習題目（因此計數為 0）

思路：

先將條件進行初步篩選，比如先查出做過高難度 sql 試卷的用戶

SELECT er.uid
FROM exam_record er
INNER JOIN examination_info ei
WHERE er.exam_id = ei.exam_id
AND ei.tag = 'SQL'
AND ei.difficulty = 'hard'

在這里插入圖片描述
然后根據題目要求，接著再往里疊條件即可；

但是這里又要注意：

第一：不能YEAR(submit_time)= 2021這個條件放到最后，要在ON條件里，因為左連接存在返回左表全部行，右表為 null 的情形，放在 JOIN條件的 ON 子句中的目的是為了確保在連接兩個表時，只有滿足年份條件的記錄會進行連接。這樣可以避免其他年份的記錄被包含在結果中。即 1001 做過 2021 年的試卷，但沒有練習過，如果把條件放到最后，就會排除掉這種情況。

第二，必須是COUNT(distinct er.exam_id) exam_cnt, COUNT(distinct pr.id) question_cnt，要加 distinct，因為有左連接產生很多重復值。

答案：

SELECT er.uid,COUNT(DISTINCT er.exam_id) AS exam_cnt,COUNT(DISTINCT pr.id) AS question_cnt
FROM exam_record er
LEFT JOIN practice_record pr ON er.uid = pr.uid
AND YEAR(er.submit_time) = 2021
AND YEAR(pr.submit_time) = 2021
WHERE er.uid IN (SELECT er.uidFROM exam_record erINNER JOIN examination_info ei ON	er.exam_id = ei.exam_idINNER JOIN user_info ui ON ui.uid = er.uidWHERE ei.tag = 'SQL'AND ei.difficulty = 'hard'AND LEVEL = 7GROUP BY er.uidHAVING AVG(score) > 80
)
GROUP BY er.uid
ORDER BY exam_cnt,question_cnt DESC

能細心的小伙伴會發現，為什么明明將條件限制了tag = 'SQL' AND difficulty = 'hard'，但是用戶 1003 仍然能查出兩條考試記錄，其中一條的考試tag為 C++; 這是由于LEFT JOIN的特性，即使沒有與右表匹配的行，左表的所有記錄仍然會被保留。

5.2、每個 6/7 級用戶活躍情況（困難）

描述：

現有用戶信息表 user_info（uid 用戶 ID，nick_name 昵稱, achievement 成就值,level等級, job 職業方向, register_time 注冊時間），示例數據如下：

iduidnick_nameachievementleveljobregister_time
11001機器人 1 號31007算法2020-01-01 10:00:00
21002機器人 2 號23007算法2020-01-01 10:00:00
31003機器人 3 號25007算法2020-01-01 10:00:00
41004機器人 4 號12005算法2020-01-01 10:00:00
51005機器人 5 號16006C++2020-01-01 10:00:00
61006機器人 6 號20007C++2020-01-01 10:00:00

現有試卷信息表 examination_info（exam_id：試卷 ID, tag：試卷類別, difficulty：試卷難度, duration：考試時長, release_time：發布時間），示例數據如下：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-09-01 06:00:00
29002C++easy602021-09-01 06:00:00
39003算法medium802021-09-01 10:00:00

現有試卷作答記錄表 exam_record（uid 用戶 ID,exam_id 試卷 ID, start_time 開始作答時間, submit_time 交卷時間, score 得分） 示例數據如下：

uidexam_idstart_timesubmit_timescore
100190012021-09-01 09:01:012021-09-01 09:31:0078
100190012021-09-01 09:01:012021-09-01 09:31:0081
100590012021-09-01 19:01:012021-09-01 19:30:0185
100590022021-09-01 12:01:012021-09-01 12:31:0285
100690032021-09-07 10:01:012021-09-07 10:21:5984
100690012021-09-07 10:01:012021-09-07 10:21:0181
100290012020-09-01 13:01:012020-09-01 13:41:0181
100590012021-09-01 14:01:01(NULL)(NULL)

題目練習表 practice_record（uid 用戶 ID, question_id 題目 ID, submit_time 提交時間, score 得分）：

uidquestion_idsubmit_timescore
100180012021-08-02 11:41:0160
100480012021-08-02 19:38:0170
100480022021-08-02 19:48:0190
100180022021-08-02 19:38:0170
100480022021-08-02 19:48:0190
100680022021-08-04 19:58:0194
100680032021-08-03 19:38:0170
100680032021-08-02 19:48:0190
100680032020-08-01 19:38:0180

請統計每個 6/7 級用戶總活躍月份數、2021 年活躍天數、2021 年試卷作答活躍天數、2021 年答題活躍天數，按照總活躍月份數、2021 年活躍天數降序排序。由示例數據結果輸出如下：

uidact_month_totalact_days_2021act_days_2021_exam
1006341
1001221
1005111
1002100
1003000

解釋：6/7 級用戶共有 5 個，其中 1006 在 202109、202108、202008 共 3 個月活躍過，2021 年活躍的日期有 20210907、20210804、20210803、20210802 共 4 天，2021 年在試卷作答區 20210907 活躍 1 天，在題目練習區活躍了 3 天。

思路：

這題的關鍵在于CASE WHEN THEN的使用，不然要寫很多的left join 因為會產生很多的結果集。

CASE WHEN THEN語句是一種條件表達式，用于在 SQL 中根據條件執行不同的操作或返回不同的結果。

語法結構如下：

CASEWHEN condition1 THEN result1WHEN condition2 THEN result2...ELSE result
END

在這個結構中，可以根據需要添加多個WHEN子句，每個WHEN子句后面跟著一個條件（condition）和一個結果（result）。條件可以是任何邏輯表達式，如果滿足條件，將返回對應的結果。

最后的ELSE子句是可選的，用于指定當所有前面的條件都不滿足時的默認返回結果。如果沒有提供ELSE子句，則默認返回NULL。

例如：

SELECT score,CASEWHEN score >= 90 THEN '優秀'WHEN score >= 80 THEN '良好'WHEN score >= 60 THEN '及格'ELSE '不及格'END AS grade
FROM student_scores;

在上述示例中，根據學生成績（score）的不同范圍，使用 CASE WHEN THEN 語句返回相應的等級（grade）。如果成績大于等于 90，則返回"優秀"；如果成績大于等于 80，則返回"良好"；如果成績大于等于 60，則返回"及格"；否則返回"不及格"。

那了解到了上述的用法之后，回過頭看看該題，要求列出不同的活躍天數。

count(distinct act_month) as act_month_total,
count(distinct case when year(act_time)='2021'then act_day end) as act_days_2021,
count(distinct case when year(act_time)='2021' and tag='exam' then act_day end) as act_days_2021_exam,
count(distinct case when year(act_time)='2021' and tag='question'then act_day end) as act_days_2021_question

這里的 tag 是先給標記，方便對查詢進行區分，將考試和答題分開。

找出試卷作答區的用戶

SELECT uid,exam_id AS ans_id,start_time AS act_time,DATE_FORMAT(start_time,'%Y%m') AS act_month,DATE_FORMAT(start_time,'%Y%m%d') AS act_day,'exam' AS tag
FROM exam_record

緊接著就是答題作答區的用戶

SELECT uid,question_id AS ans_id,submit_time AS act_time,DATE_FORMAT(submit_time,'%Y%m') AS act_month,DATE_FORMAT(submit_time,'%Y%m%d') AS act_day,'question' AS tag
FROM practice_record

最后將兩個結果進行UNION 最后別忘了將結果進行排序 （這題有點類似于分治法的思想）

答案：

SELECT user_info.uid,count(distinct act_month) as act_month_total,count(distinct case when year(act_time)='2021'then act_day end) as act_days_2021,count(distinct case when year(act_time)='2021' and tag='exam' then act_day end) as act_days_2021_exam,count(distinct case when year(act_time)='2021' and tag='question'then act_day end) as act_days_2021_question
FROM (SELECT uid,exam_id AS ans_id,start_time AS act_time,DATE_FORMAT(start_time,'%Y%m') AS act_month,DATE_FORMAT(start_time,'%Y%m%d') AS act_day,'exam' AS tagFROM exam_recordUNION ALLSELECT uid,question_id AS ans_id,submit_time AS act_time,DATE_FORMAT(submit_time,'%Y%m') AS act_month,DATE_FORMAT(submit_time,'%Y%m%d') AS act_day,'question' AS tagFROM practice_record
) total
RIGHT JOIN user_info ON total.uid = user_info.uid
WHERE user_info.LEVEL IN (6,7)
GROUP BY user_info.uid
ORDER BY act_month_total DESC,act_days_2021 DESC

在這里插入圖片描述

好文推薦：
《【SQL】SQL常見面試題總結（1）》
《【SQL】SQL常見面試題總結（2）》

在這里插入圖片描述

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/web/13095.shtml
繁體地址，請注明出處：http://hk.pswp.cn/web/13095.shtml
英文地址，請注明出處：http://en.pswp.cn/web/13095.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

藍橋杯 EDA 組 歷屆國賽真題解析

藍橋杯 EDA 組 歷屆國賽真題解析

一、2021年國賽真題 1.1 CN3767 太陽能充電電路 CN3767 是具有太陽能電池最大功率點跟蹤功能的 4A&#xff0c;12V 鉛酸電池充電管理集成電路。 最大功率點應指的是電池板的輸出電壓&#xff0c;跟蹤電壓其做保護。當然 CN3767 也可以直接使用直流充電&#xff0c;具體可以閱讀…
閱讀更多...
ROS 2邊學邊練（49）-- 生成URDF文件

ROS 2邊學邊練（49）-- 生成URDF文件

前言 大多數機器人學家都在團隊中工作&#xff0c;這些團隊中往往包括機械工程師&#xff0c;他們負責開發機器人的CAD模型。與手動創建URDF&#xff08;統一機器人描述格式&#xff09;文件不同&#xff0c;可以從許多不同的CAD和建模程序中導出URDF模型。這些導出工具通常…
閱讀更多...
[POJ-1321]棋盤問題

[POJ-1321]棋盤問題

題源:POJ-1321 深搜板子題&#xff0c;非常基礎&#xff0c;難度不大 思路1&#xff1a;廣搜行 深搜列 #include<iostream> #include<cstring> using namespace std; const int MAX9; int a,b,ans; char m[MAX][MAX]; //深搜列&#xff0c;廣搜行 bool h[MAX]; v…
閱讀更多...
DS高階：跳表

DS高階：跳表

一、skiplist 1.1 skiplist的概念 skiplist本質上也是一種查找結構&#xff0c;用于解決算法中的查找問題&#xff0c;跟平衡搜索樹和哈希表的價值是一樣的&#xff0c;可以作為key或者key/value的查找模型。skiplist是由William Pugh發明的&#xff0c;最早出現于他在1990年發…
閱讀更多...
Python學習之路 | Python基礎語法（一）

Python學習之路 | Python基礎語法（一）

數據類型 Python3 中常見的數據類型有&#xff1a; Number&#xff08;數字&#xff09;String&#xff08;字符串&#xff09;bool&#xff08;布爾類型&#xff09;List&#xff08;列表&#xff09;Tuple&#xff08;元組&#xff09;Set&#xff08;集合&#xff09;Dict…
閱讀更多...
鴻蒙HDC命令行工具：模擬操作

鴻蒙HDC命令行工具：模擬操作

模擬操作 uinput用于輸入模擬操作&#xff0c;其命令幫助手冊為&#xff1a; > hdc shell uinput --help Usage: uinput <option> <command> <arg>... The option are: -M --mouse //模擬鼠標操作 commands for mouse: -m <dx> <d…
閱讀更多...
【Image captioning】基于檢測模型網格特征提取——以Sydeny為例

【Image captioning】基于檢測模型網格特征提取——以Sydeny為例

【Image captioning】基于檢測模型網格特征提取——以Sydeny為例 今天，我們將重點探討如何利用Faster R-CNN檢測模型來提取Sydeny數據集的網格特征。具體而言，這一過程涉及通過Faster R-CNN模型對圖像進行分析，進而抽取出關鍵區域的特征信息，這些特征在網格結構中被系統地…
閱讀更多...
1金融風控相關業務介紹

1金融風控相關業務介紹

金融風控相關業務介紹 學習目標 知道常見信貸風險知道機器學習風控模型的優勢知道信貸領域常用術語含義1 信貸&風控介紹 信貸業務，就是貸款業務，是商業銀行和互聯網金融公司最重要的資產業務和主要贏利手段 通過放款收回本金和利息，扣除成本后獲得利潤。貸款平臺預測有…
閱讀更多...
java中什么是方法的返回值？方法有哪幾種類型？

java中什么是方法的返回值？方法有哪幾種類型？

在Java中&#xff0c;方法的返回值是指方法執行后返回給調用者的結果。返回值可以是任何數據類型&#xff0c;包括基本數據類型&#xff08;如int、float&#xff09;和引用數據類型&#xff08;如String、對象&#xff09;。返回值的主要作用是將方法執行的結果傳遞給調用該方…
閱讀更多...
springboot集成dubbo實現微服務系統

springboot集成dubbo實現微服務系統

目錄 1.說明 2.示例 3.總結 1.說明 dubbo官網&#xff1a;https://cn.dubbo.apache.org/zh-cn/ Apache Dubbo 是一款 RPC 服務開發框架&#xff0c;用于解決微服務架構下的服務治理與通信問題&#xff0c;支持多種語言&#xff0c;官方提供了 Java、Golang 等多語言 SDK 實…
閱讀更多...
什么是Vue.js? Vue.js簡介

什么是Vue.js? Vue.js簡介

什么是Vue.js? Vue.js簡介 Vue.js是一種用于構建用戶界面的前端框架。它是目前非常流行的JavaScript框架之一&#xff0c;被廣泛應用于單頁應用和響應式網頁開發。 Vue.js具有以下特點和優勢&#xff1a; 輕量級&#xff1a; Vue.js的文件體積很小&#xff0c;加載速度快&…
閱讀更多...
代碼隨想錄--鏈表--反轉鏈表

代碼隨想錄--鏈表--反轉鏈表

題目 題意&#xff1a;反轉一個單鏈表。 示例: 輸入: 1->2->3->4->5->NULL 輸出: 5->4->3->2->1->NULL 思路 如果再定義一個新的鏈表&#xff0c;實現鏈表元素的反轉&#xff0c;其實這是對內存空間的浪費。 其實只需要改變鏈表的next指針的…
閱讀更多...
GPU學習記一下線程分組相關

GPU學習記一下線程分組相關

在compute的時候&#xff0c;是要dispatch一個數量的代表分了多少塊任務集&#xff0c;dispatch的塊內部也是有一個數量的&#xff0c;那么這些值怎么取的呢 內部&#xff0c;N卡32 外面dispatch的數量就是all/32 然后細說這個值 這有一個叫core的東西&#xff0c;就是相當于th…
閱讀更多...
嵌入式學習-PWM輸出比較

嵌入式學習-PWM輸出比較

簡介 PWM技術 輸出比較框圖介紹 定時器部分 比較器控制部分 輸出控制部分 相關寄存器
閱讀更多...
（5.4–5.10）投融資周報｜共38筆公開投融資事件，基礎設施領跑，游戲融資活躍

（5.4–5.10）投融資周報｜共38筆公開投融資事件，基礎設施領跑，游戲融資活躍

5月4日至5月10日期間&#xff0c;加密市場共發生38筆投融資事件&#xff0c;其中基礎設施18筆、游戲5 筆、其他4 筆、DeFi 3筆、Depin 3 筆、CeFi 2筆、NFT2筆、 RWA1筆。 本周千萬美金以上融資有5筆&#xff1a; 加密貨幣交易公司Arbelos完成了一輪2800 萬美元的種子輪融資&…
閱讀更多...
智慧園區EasyCVR視頻智能管理方案：構建高效安全園區新視界

智慧園區EasyCVR視頻智能管理方案：構建高效安全園區新視界

一、背景分析 園區作為城市的基本單元&#xff0c;是最重要的人口和產業聚集區。根據行業市場調研&#xff0c;90%以上城市居民工作與生活在園區進行&#xff0c;80%以上的GDP和90%以上的創新在園區內產生&#xff0c;可以說“城市&#xff0c;除了馬路都是園區”。 園區形態…
閱讀更多...
C++ static_cast學習

C++ static_cast學習

static_cast可實現&#xff0c; 1 基本類型之間的轉換 2 void指針轉換為任意基本類型的指針 3 用于有繼承關系的子類與父類之間的指針或引用的轉換 用于基本類型轉化時&#xff0c;會損失精度類似于C語言的強制轉化&#xff1b; 下面先看一下void指針的轉換&#xff1b; …
閱讀更多...
手動實現Promise

手動實現Promise

// 定義異步調用的主類&#xff0c;名為 MyPromise class MyPromise {// 執行器接收 resolve 和 reject 方法來改變 promise 的狀態constructor(executor) {// 初始化狀態為 "pending"this.state "pending";// 初始化值為 undefinedthis.value undefined…
閱讀更多...
鏡像抑制和鏡像衰減有什么不同

鏡像抑制和鏡像衰減有什么不同

在很多無線產品接收機手冊中&#xff0c;我們會看到兩個參數&#xff0c;一個是鏡像抑制&#xff08;Image Rejection&#xff09;&#xff0c;另一個是鏡像衰減&#xff08;Image Attention&#xff09;&#xff0c;但這兩者究竟有什么不同&#xff0c;一直比較疑惑&#xff0…
閱讀更多...
AI學習指南線性代數篇-奇異值分解

AI學習指南線性代數篇-奇異值分解

AI學習指南線性代數篇-奇異值分解 一、概述 在人工智能領域&#xff0c;線性代數是一項非常重要的基礎知識&#xff0c;而奇異值分解&#xff08;Singular Value Decomposition, SVD&#xff09;作為線性代數中的一種重要工具&#xff0c;被廣泛應用于機器學習、數據科學等領…
閱讀更多...
最新文章

Copyright @ 2022~2023