目錄

1嵌套子查詢

1.1月均完成試卷數(shù)不小于3的用戶愛作答的類別

1.2月均完成試卷數(shù)不小于3的用戶愛作答的類別

?編輯1.3?作答試卷得分大于過80的人的用戶等級(jí)分布

2合并查詢

2.1每個(gè)題目和每份試卷被作答的人數(shù)和次數(shù)

2.2分別滿足兩個(gè)活動(dòng)的人

3連接查詢

3.1滿足條件的用戶的試卷完成數(shù)和題目練習(xí)數(shù)

3.2?每個(gè)6/7級(jí)用戶活躍情況

1嵌套子查詢

1.1月均完成試卷數(shù)不小于3的用戶愛作答的類別

我的代碼：思路就是這么個(gè)思路，反正沒有搞出來當(dāng)月均完成試卷數(shù)

select tag,count(submit_time) tag_cnt
from exam_record er join examination_info ei
on er.exam_id = ei.exam_id
where uid in (當(dāng)月均完成試卷數(shù)>=3)
group by tag
order by tag_cnt desc

反正沒有搞出來當(dāng)月均完成試卷數(shù)，報(bào)錯(cuò)：?

大佬正確答案：

居然和我的差不多，我就分組的時(shí)候少了uid，還有按照uid進(jìn)行分組。此外，作答次數(shù)=count(start_time)，而不是提交次數(shù)。

select tag, count(start_time) as tag_cnt
from exam_record er inner join examination_info ei
on er.exam_id = ei.exam_id
where uid in 
(select uid
from exam_record er 
group by uid, month(start_time)
having count(submit_time) >= 3)
group by tag
order by tag_cnt desc

復(fù)盤：

（1）uid,month(submit_time)是啥呢，如果原來只是按照month(submit_time)進(jìn)行分組，1002,1003,1005都有多個(gè)

?（2）如果按照uid,month(submit_time)進(jìn)行分組，情況如下

（3）這么如果只是按照month(submit_time)?分組，uid,month(submit_time)只有9和null兩種情況，當(dāng)使用GROUP BY子句時(shí)，NULL值將被視為一個(gè)獨(dú)立的分組，并在結(jié)果集中顯示一個(gè)額外的分組來表示它。

（4）結(jié)果顯示只有1002,1005這兩個(gè)用戶滿足要求，然后查找這兩個(gè)用戶的作答的類別及作答次數(shù)。

（5）驗(yàn)證：where uid =1002 or uid = 1005? 等價(jià)于 子查詢的效果

還有一種大佬做法是：

select tag,count(start_time) tag_cnt
from exam_record er join examination_info ei
on er.exam_id = ei.exam_id
-- where uid =1002 or uid = 1005 
WHERE er.uid IN (SELECT uidFROM exam_recordGROUP BY uidHAVING COUNT(submit_time) / COUNT(DISTINCT DATE_FORMAT(submit_time, "%Y%m")) >= 3
)
group by tag
order by tag_cnt desc

?這樣出來的兩個(gè)用戶也是1002和1005：

• 相當(dāng)于：月均完成試卷數(shù) = 總完成次數(shù)/哪些月份提交了數(shù)據(jù)

COUNT(DISTINCT DATE_FORMAT(submit_time, "%Y%m"))=1，所以答案一樣的。

COUNT(DISTINCT DATE_FORMAT(submit_time, "%Y%m"))中的distinct很重要：

1.2月均完成試卷數(shù)不小于3的用戶愛作答的類別

我的代碼：答案錯(cuò)誤，但是我能發(fā)現(xiàn)的的改了，

（1）SQL類，（2）當(dāng)天，（3）作答人數(shù)

select er.exam_id,
any_value(count(er.submit_time)) uv,
round(avg(er.score),1) avg_score
from examination_info ei join exam_record er
on ei.exam_id = er.exam_id
where ei.tag = "SQL" 
and day(submit_time)=day(release_time)
and er.uid in 
(select uid 
from user_info
where level > 5)
group by er.exam_id
order by uv desc,avg_score asc

正確代碼：

select er.exam_id,
any_value(count(distinct er.uid)) uv,
round(avg(er.score),1) avg_score
from examination_info ei join exam_record er
on ei.exam_id = er.exam_id
where ei.tag = "SQL" 
and date_format(submit_time,'%Y%m%d')=date_format(release_time,'%Y%m%d')
and er.uid in 
(select uid 
from user_info
where level > 5)
group by er.exam_id
order by uv desc,avg_score asc

復(fù)盤：

（1）同一天，不能用day函數(shù)，0901和0201的day都是1，但是不是同一天。

（2）計(jì)算人數(shù)時(shí)，要加distinct才對(duì)：

原數(shù)據(jù)有這種離譜的情況？？

1.3?作答試卷得分大于過80的人的用戶等級(jí)分布

我的正確代碼：直接三表連接

select level,count(level) level_cnt
from user_info u 
join exam_record er
on u.uid = er.uid
join examination_info ei
on ei.exam_id = er.exam_id
where ei.tag = 'SQL'
and er.score>80
group by level

嵌套子查詢的方法代碼：

SELECT level, 
COUNT(level) AS level_cnt
FROM user_info
WHERE uid IN (SELECT DISTINCT uidFROM exam_recordWHERE score > 80AND exam_id IN (SELECT exam_id FROM examination_info WHERE tag = 'SQL'))
GROUP BY level
ORDER BY level_cnt DESC;

2合并查詢

2.1每個(gè)題目和每份試卷被作答的人數(shù)和次數(shù)

我的代碼：分別查詢?nèi)缓笥胾nion all合并起來，但是答案錯(cuò)了

select exam_id tid,
count(distinct er.uid) uv,
count(distinct pr.submit_time) pv
from exam_record er join practice_record pr
using(uid)
group by exam_idunion allselect question_id tid,
count(distinct er.uid) uv,
count(distinct pr.submit_time) pv
from exam_record er join practice_record pr
using(uid)
group by question_id

正確答案：

select * from 
(SELECT exam_id tid,count(DISTINCT uid) uv,count(uid) pv from exam_record
group by exam_id
order by uv desc,pv desc)a
UNION ALL
SELECT * FROM
(SELECT question_id tid,count(DISTINCT uid) uv,count(uid) pv from practice_record
GROUP BY question_id
order by uv desc,pv desc)b

我的代碼改正：這個(gè)題最后不要合并，題目和試卷在不同的表里，分別查詢?cè)诤喜⒕秃昧?/p>

select exam_id tid,
count(distinct er.uid) uv,
count(er.uid) pv
from exam_record er
group by exam_idunion allselect question_id tid,
count(distinct pr.uid) uv,
count(pr.uid) pv
from practice_record pr
group by question_id

還沒排序：

但是使用 union 和 多個(gè)order by 不加括號(hào) 【報(bào)錯(cuò)】，order by 在 union 連接的子句不起作用，但是在子句的子句中起作用。

方法一：所以加兩個(gè)order的話正確要這樣寫：

#正確代碼
select * from 
(
select exam_id as tid,count(distinct uid) as uv,count(uid) as pv
from exam_record a
group by exam_id
order by uv desc, pv desc
) a
union
select * from 
(
select question_id as tid,count(distinct uid) as uv,count(uid) as pv
from practice_record b
group by question_id
order by uv desc, pv desc
) attr

方法二：或者利用left(str,length) 函數(shù)： str左邊開始的長度為 length 的子字符串，在本例中為‘9’和‘8’。

order by left(tid,1) desc,uv desc,pv desc

解釋：試卷編號(hào)以‘9’開頭、題目編號(hào)以‘8’開頭，對(duì)編號(hào)進(jìn)行降序就是對(duì)"試卷"和"題目"分別進(jìn)行排序。

(#每份試卷被作答的人數(shù)和次數(shù)selectexam_id as tid,count(distinct uid) as uv,count(*) as pv
from exam_record
group by exam_id
)
union
(#每個(gè)題目被作答的人數(shù)和次數(shù)selectquestion_id as tid,count(distinct uid) as uv,count(*) as pv
from practice_record
group by question_id
)
#分別按照"試卷"和"題目"的uv & pv降序顯示
order by left(tid,1) desc,uv desc,pv desc

2.2分別滿足兩個(gè)活動(dòng)的人

我的垃圾代碼：不知道新的值怎么弄

(select uidfrom exam_recordgroup by 1001having score>85
)tselect uid	t.activity
from examination_info ei join exam_record er
on ei.exam_id = er.exam_id

大佬代碼：

(select uid,'activity1' as activity
from exam_record er
where year(start_time)='2021'
group by uid
having min(score)>=85)
union ALL
(select uid,'activity2' as activity
from exam_record er left join examination_info ei on er.exam_id=ei.exam_id
where year(start_time)='2021' and ei.difficulty='hard' and score>=80 
and timestampdiff(second,er.start_time,er.submit_time)<= ei.duration*30
group by uid)
order by uid;

復(fù)盤：

（1）select uid,'activity1' as activity...，這樣就把activity這一列就設(shè)置出來了。

（2）時(shí)間差函數(shù)：timestampdiff，如計(jì)算差多少分鐘，timestampdiff(minute,時(shí)間1,時(shí)間2)，是時(shí)間2-時(shí)間1，單位是minute。

這里是至少有一次用了一半時(shí)間就完成：

完成時(shí)間<=考試時(shí)長/2 （單位為分鐘minute）

完成時(shí)間<=考試時(shí)長*60/2 =考試時(shí)長*30（單位為秒second）

timestampdiff(second,er.start_time,er.submit_time)<= ei.duration*30

（3）每次試卷得分都能到85分，相當(dāng)于最低分min>=85

3連接查詢

3.1滿足條件的用戶的試卷完成數(shù)和題目練習(xí)數(shù)

我的報(bào)錯(cuò)代碼：看來不是這么簡單粗暴的事情?

select u.uid,
count(er.submit_time) exam_cnt,
count(pr.submit_time) question_cnt
from user_info u join exam_record er
on u.uid = er.uid
join practice_record pr
on pr.uid = u.uid
join examination_info ei
on ei.exam_id = er.exam_id
where year(er.submit_time)='2021'
group by u.uid
having ei.tag = 'SQL'
and ei.difficulty = 'hard'
and u.level = 7
and avg(er.score)>80

正確代碼：

# select er.uid as uid,
# count(distinct er.submit_time) as exam_cnt,
# count(distinct pr.submit_time) as question_cnt
select er.uid as uid,
count(distinct er.exam_id) as exam_cnt,
count(distinct pr.id) as question_cntfrom exam_record er 
left join practice_record pr 
on er.uid=pr.uid 
and year(er.submit_time)=2021 
and year(pr.submit_time)=2021where er.uid in(select er.uidfrom exam_record er left join examination_info ei on er.exam_id = ei.exam_idleft join user_info ui on er.uid = ui.uid where tag='SQL' and difficulty='hard' and level = 7group by er.uidhaving avg(score) > 80) 
group by er.uid
order by exam_cnt,question_cnt desc

復(fù)盤：

有4個(gè)表，很多個(gè)條件

（1）先通過子查詢中連接，er,ui和ei篩選出高難度SQL試卷得分平均值大于80并且是7級(jí)的紅名大佬（返回用戶uid）

（2） 再統(tǒng)計(jì)這些大佬的2021年試卷總完成次數(shù)，和題目總練習(xí)次數(shù)

（3）注意第（2）步中連接是左連接，不應(yīng)該出現(xiàn)試卷為null，題目不為null的情況！

from exam_record er left join practice_record pr

（4）不懂為什么不能用 er.submit_time， pr.submit_time來計(jì)算

# select er.uid as uid,
# count(distinct er.submit_time) as exam_cnt,
# count(distinct pr.submit_time) as question_cnt
select er.uid as uid,
count(distinct er.exam_id) as exam_cnt,
count(distinct pr.id) as question_cnt

3.2?每個(gè)6/7級(jí)用戶活躍情況

我的錯(cuò)誤代碼：

總活躍月份數(shù)？其他都是2021年的，活躍是啥意思？

select er.uid,
# act_month_total,
count(er.start_time) act_days_2021,
count(er.submit_time) act_days_2021_exam,
count(pr.submit_time) act_days_2021_question
from exam_record er left join practice_record pr
on er.uid=pr.uid
where year(er.submit_time)=2021
and er.uid in
(select uid 
from user_info
where level = 7 or level = 6)
group by er.uid

正確代碼

selectuser_info.uid,count(distinct act_month) as act_month_total,count(distinct casewhen year (act_time) = '2021' then act_dayend) as act_days_2021,count(distinct casewhen year (act_time) = '2021'and tag = 'exam' then act_dayend) as act_days_2021_exam,count(distinct casewhen year (act_time) = '2021'and tag = 'question' then act_dayend) as act_days_2021_question
from(SELECTuid,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 tagfromexam_recordUNION ALLselectuid,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 tagfrompractice_record) totalright join user_info on total.uid = user_info.uid
whereuser_info.level in (6, 7)
group byuser_info.uid
order byact_month_total desc,act_days_2021 desc

復(fù)盤

（1）case?when是關(guān)鍵

（2）2021年活躍天數(shù) = 2021年試卷作答活躍天數(shù) + 2021年答題活躍天數(shù)

則?exam?as?tag?和?practice?as?tag，自定義一列，為了區(qū)分是考試還是練習(xí)，便于區(qū)別計(jì)算

（3）右連接?total?? ? right join user_info??? ? on total.uid = user_info.uid

因?yàn)樽越M合的total表：沒有1003

原本的user_info表：

但是6/7級(jí)的大佬中是有1003的

?