【牛客刷題】SQL專項錯題記錄四

1.牛客每天有很多人登入，請你統計一下牛客每個使用者查詢刷題資訊，包括: 使用者的名字，以及截止到某天，累計總共通過了多少題。 不存在沒有登入卻刷題的情況，但是存在登入了沒刷題的情況，不會存在刷題表裡面，有送出代碼沒有通過的情況，但是會記錄在刷題表裡，隻不過通過數目是0。

有一個登入(login)記錄表，簡況如下:

請你寫出一個sql語句查詢刷題資訊，包括: 使用者的名字，以及截止到某天，累計總共通過了多少題，并且查詢結果先按照日期升序排序，再按照姓名升序排序，有登入卻沒有刷題的哪一天的資料不需要輸出，上面的例子查詢結果如下:

考察點：視窗函數

select u.name as u_n,pn.date as date
, sum(number) over (partition by pn.user_id order by pn.date)as ps_num 
from passing_number pn
inner join user u on pn.user_id=u.id
order by pn.date,u.name           

2.牛客每次考試完，都會有一個成績表(grade)，如下:

請你寫一個sql語句查詢各個崗位分數升序排列之後的中位數位置的範圍，并且按job升序排序，結果如下:

第1行表示C++崗位的中位數位置範圍為[2,2]，也就是2。因為C++崗位總共3個人，是奇數，是以中位數位置為2是正确的(即位置為2的10000是中位數)

第2行表示Java崗位的中位數位置範圍為[1,2]。因為Java崗位總共2個人，是偶數，是以要知道中位數，需要知道2個位置的數字，而因為隻有2個人，是以中位數位置為[1,2]是正确的(即需要知道位置為1的12000與位置為2的13000才能計算出中位數為12500)

第3行表示前端崗位的中位數位置範圍為[2,2]，也就是2。因為前端崗位總共3個人，是奇數，是以中位數位置為2是正确的(即位置為2的11000是中位數)

(注意: sqlite 1/2得到的不是0.5，得到的是0，隻有1*1.0/2才會得到0.5，sqlite四舍五入的函數為round，sqlite不支援floor函數，支援cast(x as integer) 函數，不支援if函數，支援case when ...then ...else ..end函數)

考察點：case when

SELECT 
job,
CASE WHEN total%2=0 THEN total/2 ELSE (total+1)/2 END AS `start`,
CASE WHEN total%2=0 THEN total/2+1 ELSE (total+1)/2 END AS `end`
FROM
(SELECT job,COUNT(1) AS total
FROM grade
GROUP BY job)t;           

3.牛客每次考試完，都會有一個成績表(grade)，如下:

請你寫一個sql語句查詢各個崗位分數的中位數位置上的所有grade資訊，并且按id升序排序，結果如下:

select t.id, t.job, t.score, t.rank
from 
(
    select *, count(*) over(partition by job) as c, row_number()over(partition by job order by score desc) as rank
    from grade
) as t
where rank>=(t.c+1)/2
and rank<=(t.c+2)/2
group by id;