牛客网SQL基础强化

SQL40 分页显示第三页的结果

SELECT *
FROM user_profile
ORDER BY device_id
LIMIT 6, 3

SQL41 筛选出学校名中包含特定关键词的用户

SELECT id, device_id, university
FROM user_profile
WHERE university REGEXP '^(北京|上海).*(职业|专科|成人).*(学院|校区)$'

SQL42 每个学校的平均年龄和平均绩点及整体情况

SELECT IFNULL(university, '总体') AS university, ROUND(AVG(age), 3) AS avg_age, ROUND(AVG(gpa), 3) AS avg_gpa
FROM user_profile
GROUP BY university WITH ROLLUP
ORDER BY university

SQL43 每个学校有哪些年龄的学生

SELECT university, GROUP_CONCAT(DISTINCT age ORDER BY age) AS age_concat
FROM user_profile
GROUP BY university

SQL44 学生数不大于2的学校的学生情况

SELECT university, COUNT(1) AS cnt, GROUP_CONCAT(device_id SEPARATOR ';') AS device_list
FROM user_profile
GROUP BY university
HAVING cnt <= 2

SQL45 每个年龄的学生在各学校最大数量

SELECT age, MAX(cnt) AS max_cnt
FROM (
    SELECT university, age, COUNT(1) AS cnt
    FROM user_profile
    GROUP BY university, age
) tmp
GROUP BY age
ORDER BY max_cnt DESC

SQL46 存在绩点大于该校平均绩点时的学生信息

SELECT usr.device_id, usr.university
FROM user_profile AS usr INNER JOIN (
    SELECT university, AVG(gpa) AS avg_gpa
    FROM user_profile
    GROUP BY university
    HAVING AVG(gpa) > 3.6
) univ ON usr.university = univ.university
WHERE usr.gpa > univ.avg_gpa
ORDER BY device_id DESC

SQL47 平均绩点大于3.6且总人数大于2的学校里学生情况

SELECT university, COUNT(1) AS total_cnt, SUM(IF(gender = 'male', 1, 0)) AS male_cnt, SUM(IF(gender = 'female', 1, 0)) AS female_cnt, SUM(IF(age < 23, 1, 0)) AS lt23_cnt
FROM user_profile
GROUP BY university
HAVING AVG(gpa) > 3.6 AND COUNT(1) > 2

SQL48 所有用户对高难度题目的刷题数

SELECT usr.device_id, CONVERT(SUM(IF(question.difficult_level = 'hard', 1, 0)), SIGNED) AS question_cnt
FROM user_profile AS usr LEFT JOIN question_practice_detail AS practice ON usr.device_id = practice.device_id LEFT JOIN question_detail AS question ON practice.question_id = question.question_id
GROUP BY usr.device_id
ORDER BY question_cnt

SQL49 北京大学或练题数大于2的学生ID

SELECT device_id
FROM user_profile
WHERE university = '北京大学'
UNION
SELECT usr.device_id
FROM user_profile AS usr INNER JOIN question_practice_detail AS practice ON usr.device_id = practice.device_id
GROUP BY usr.device_id
HAVING COUNT(1) > 2
ORDER BY device_id DESC

SQL50 各年级学生的平均绩点

SELECT
    CASE graduate_year
        WHEN 2022 THEN '大四'
        WHEN 2023 THEN '大三'
        WHEN 2024 THEN '大二'
        WHEN 2025 THEN '大一'
    END AS grade,
    ROUND(AVG(gpa), 2) AS avg_gpa
FROM
    user_profile
WHERE
    graduate_year BETWEEN 2022 AND 2025
GROUP BY
    graduate_year
ORDER BY
    avg_gpa DESC

SQL51 每个用户的第一有效信息

SELECT device_id,
       CASE WHEN answer_cnt IS NOT NULL THEN CONCAT('答题数:', answer_cnt)
            WHEN gpa IS NOT NULL THEN CONCAT('gpa:', gpa)
            WHEN age IS NOT NULL THEN CONCAT('年龄:', age)
            ELSE '暂无关键信息'
       END AS key_info
FROM
    user_profile
ORDER BY
    device_id

SQL52 每月各旬有多少人练题

SELECT 
  dt_range,
  COUNT(DISTINCT device_id) AS cnt
FROM
  (
    SELECT  
      device_id,
      CONCAT(
        SUBSTRING(event_date, 1, 4),
        '年',
        SUBSTRING(event_date, 6, 2),
        '月',
        CASE
          WHEN DAY(event_date) BETWEEN 1 AND 9 THEN '上旬'
          WHEN DAY(event_date) BETWEEN 10 AND 19 THEN '中旬'
          ELSE '下旬'
        END
      ) AS dt_range
    FROM
      question_practice_detail
  ) tmp
GROUP BY
  dt_range
ORDER BY 
  SUBSTRING(dt_range, 1, 8) DESC,
  cnt DESC

SQL53 用户每月最后一周的练题数

SELECT device_id, DATE_FORMAT(event_date, '%Y年%m月') AS ym, COUNT(1) AS cnt 
FROM question_practice_detail 
WHERE event_date > DATE_SUB(LAST_DAY(event_date), INTERVAL 1 WEEK) 
GROUP BY device_id, DATE_FORMAT(event_date, '%Y年%m月') 
ORDER BY device_id, ym

SQL54 用户昵称超长的用户

SELECT device_id, nick_name, CHAR_LENGTH(nick_name) AS nick_len
FROM user_submit 
WHERE CHAR_LENGTH(nick_name) > 4
ORDER BY device_id DESC

SQL55 用户名规范化

SELECT device_id, CONCAT(UCASE(SUBSTR(user_name, 1, 1)), LCASE(SUBSTR(user_name, 2))) AS user_name
FROM (
    SELECT device_id, SUBSTRING_INDEX(blog_url, '/', -1) AS user_name 
    FROM user_submit
) tmp
ORDER BY device_id DESC

SQL56 博客地址修复

SELECT device_id, REGEXP_REPLACE(TRIM(blog_url), 'http:/([^/])', 'http://$1') AS blog_url 
FROM user_submit

SQL57 最后一个匹配上的位置

SELECT 
    device_id, 
    CHAR_LENGTH(nick_name) - CHAR_LENGTH(SUBSTRING_INDEX(nick_name, 'abc', -1)) - 2 AS last_idx, 
    CONCAT('abc', SUBSTRING_INDEX(nick_name, 'abc', -1)) AS check_str 
FROM user_submit 
WHERE LOCATE('abc', nick_name) > 0
ORDER BY device_id DESC

SQL58 任意两个连续自然月练题次数大于1的用户

WITH practice_per_month AS (
    SELECT device_id, DATE_FORMAT(event_date, '%Y%m') AS mth
    FROM question_practice_detail
    GROUP BY device_id, DATE_FORMAT(event_date, '%Y%m')
    HAVING COUNT(1) > 1
)
SELECT DISTINCT t1.device_id 
FROM practice_per_month AS t1 JOIN practice_per_month AS t2 ON t1.device_id = t2.device_id AND PERIOD_DIFF(t2.mth, t1.mth) = 1
ORDER BY t1.device_id DESC

SQL59 截至当月的练题情况

WITH practice_stat AS (
    SELECT device_id, DATE_FORMAT(event_date, '%Y-%m') AS ym, COUNT(1) AS cnt 
    FROM question_practice_detail
    GROUP BY device_id, DATE_FORMAT(event_date, '%Y-%m')
)
SELECT 
    device_id, 
    ym, 
    SUM(cnt) OVER (PARTITION BY device_id ORDER BY ym) AS sum_cnt,
    ROUND(AVG(cnt) OVER (PARTITION BY device_id ORDER BY ym ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg3_cnt,
    SUM(cnt) OVER (ORDER BY ym) AS total_cnt 
FROM 
    practice_stat
ORDER BY 
    device_id, ym