SQL61 对于employees表中,给出奇数行的first_name
SELECT temp.first_name
FROM (
SELECT
first_name,
ROW_NUMBER() OVER () AS row_no,
RANK() OVER (ORDER BY first_name ASC) AS ranking
FROM employees
) temp
WHERE temp.ranking % 2 = 1
ORDER BY temp.row_no ASC
SQL62 出现三次以上相同积分的情况
SELECT number
FROM grade
GROUP BY number
HAVING COUNT(number) >= 3
SQL63 刷题通过的题目排名
SELECT id, number, DENSE_RANK() OVER (ORDER BY number DESC) AS t_rank
FROM passing_number
ORDER BY number DESC, id ASC
SQL64 找到每个人的任务
SELECT p.id, p.name, t.content
FROM person p LEFT JOIN task t ON p.id = t.person_id
SQL65 异常的邮件概率
SELECT `date`, ROUND(SUM(CASE type WHEN 'no_completed' THEN 1 ELSE 0 END) / COUNT(1), 3) AS p
FROM email
WHERE send_id IN (
SELECT id FROM `user`
WHERE is_blacklist = 0
) AND receive_id IN (
SELECT id FROM `user`
WHERE is_blacklist = 0
)
GROUP BY `date`
ORDER BY `date` ASC
SQL66 牛客每个人最近的登录日期(一)
SELECT user_id, MAX(`date`) AS d
FROM login
GROUP BY user_id
ORDER BY user_id
SQL67 牛客每个人最近的登录日期(二)
SELECT temp.u_n, temp.c_n, temp.date
FROM (
SELECT u.name AS u_n, c.name AS c_n, l.date, RANK() OVER (PARTITION BY l.user_id ORDER BY l.date DESC) AS ranking
FROM login l INNER JOIN `user` u ON l.user_id = u.id INNER JOIN client c ON l.client_id = c.id
) temp
WHERE temp.ranking = 1
ORDER BY u_n
SQL68 牛客每个人最近的登录日期(三)
SELECT ROUND(
(SELECT COUNT(DISTINCT l.user_id)
FROM login l
WHERE l.user_id IN (
SELECT l1.user_id
FROM login l1
WHERE l1.user_id = l.user_id AND DATEDIFF(l.date, l1.date) = 1
))
/
(SELECT COUNT(DISTINCT l.user_id)
FROM login l),
3)
AS p
SQL69 牛客每个人最近的登录日期(四)
SELECT temp.date, SUM(CASE temp.ranking WHEN 1 THEN 1 ELSE 0 END) AS new
FROM (
SELECT l.user_id, l.date, ROW_NUMBER() OVER (PARTITION BY l.user_id ORDER BY l.date) AS ranking
FROM login l
) temp
GROUP BY temp.date
ORDER BY temp.date
SQL70 牛客每个人最近的登录日期(五)
WITH new_user AS (
SELECT lr.user_id, lr.date, lr.ranking
FROM (
SELECT l.user_id, l.date, ROW_NUMBER() OVER (PARTITION BY l.user_id ORDER BY l.date) AS ranking
FROM login l
) lr
WHERE lr.ranking = 1
)
SELECT d.date, IFNULL(ROUND(ku.kept_cnt / nu.cnt, 3), 0) AS p
FROM (
SELECT DISTINCT l.date
FROM login l
) d LEFT JOIN (
SELECT n.date, COUNT(DISTINCT n.user_id) AS kept_cnt
FROM new_user n
WHERE EXISTS (
SELECT l1.user_id, l1.date
FROM login l1
WHERE n.user_id = l1.user_id AND DATEDIFF(l1.date, n.date) = 1
)
GROUP BY n.date
) ku ON d.date = ku.date LEFT JOIN (
SELECT n.date, COUNT(DISTINCT n.user_id) AS cnt
FROM new_user n
GROUP BY n.date
) nu ON d.date = nu.date
ORDER BY d.date
SQL71 牛客每个人最近的登录日期(六)
SELECT u.name AS u_n, p.date, SUM(p.number) OVER (PARTITION BY p.user_id ORDER BY p.date) AS ps_num
FROM passing_number p INNER JOIN `user` u ON p.user_id = u.id
ORDER BY p.date, u_n
SQL72 考试分数(一)
SELECT job, ROUND(AVG(score), 3) AS `avg`
FROM grade
GROUP BY job
ORDER BY `avg` DESC
SQL73 考试分数(二)
SELECT temp.id, temp.job, temp.score
FROM (
SELECT *, AVG(score) OVER (PARTITION BY job) AS avg_score
FROM grade
) AS temp
WHERE temp.score > temp.avg_score
SQL74 考试分数(三)
SELECT temp.id, temp.name, temp.score
FROM (
SELECT g.id, l.name, g.score, DENSE_RANK() OVER (PARTITION BY g.language_id ORDER BY g.score DESC) AS ranking
FROM grade g INNER JOIN `language` l ON g.language_id = l.id
) temp
WHERE temp.ranking <= 2
ORDER BY temp.name ASC, temp.score DESC, temp.id ASC
SQL75 考试分数(四)
SELECT job, FLOOR((COUNT(*) + 1) / 2) AS `start`, CEILING((COUNT(*) + 1) / 2) AS `end`
FROM grade
GROUP BY job
ORDER BY job
SQL76 考试分数(五)
SELECT temp.id, temp.job, temp.score, temp.t_rank
FROM (
SELECT g.id, g.job, g.score, ROW_NUMBER() OVER (PARTITION BY g.job ORDER BY g.score DESC) AS t_rank, COUNT(g.id) OVER (PARTITION BY g.job) AS t_count
FROM grade g
) temp
WHERE temp.t_rank IN (FLOOR((temp.t_count + 1) / 2), CEILING((temp.t_count + 1) / 2))
ORDER BY temp.id