【刷题记录】牛客SQL编程题目刷题记录~

SQL3 查询结果去重

题目连接

select distinct university 
from user_profile

关键点：

• 使用DISTINCT关键字可以在查询中进行去重
• 同样，DISTINCT关键字也可以用在count()等聚合函数中，进行去重统计count(distinct field)

SQL4 查询结果限制返回行数

题目链接

select device_id 
from user_profile 
limit 2

关键点：

• 使用limit关键字可以限制查询的行数

• 检索记录行 6-10：SELECT * FROM table LIMIT 5,5

  检索记录行 11-last：SELECT * FROM table LIMIT 10,-1

  检索前 5 个记录行：SELECT * FROM table LIMIT 5

SQL8 查找某个年龄段的用户信息

题目链接

select device_id, gender, age
from user_profile
# where age between 20 and 23
where age>=20 and age<=23

关键点：

• 使用between关键字，是双边闭区间

SQL15 查看学校名称中含北京的用户

题目链接

select device_id,age,university
from user_profile
where university like "%北京%"

关键点：

• 字符匹配
• field [NOT] LIKE
• 匹配串中可包含如下四种通配符：
  • _：匹配任意一个字符；
  • %：匹配0个或多个字符；
  • []：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；
  • [^]：不匹配[ ]中的任意一个字符。

SQL25 查找山东大学或者性别为男生的信息

题目链接

select
    device_id, gender, age, gpa
from user_profile
where university='山东大学'
 
union all
 
select
    device_id, gender, age, gpa
from user_profile
where gender='male'

关键点：

• 不去重的查询合并union all
• 其他：如果使用union会去重

SQL26 计算25岁以上和以下的用户数量

题目链接

select 
    case when age>=25 then "25岁及以上"
    else "25岁以下" END age_cut, count(1) number
from user_profile
group by age_cut

关键点：

• 使用case when ... then ... end作为字段
• 同样他也可以用在聚合函数中，对符合要求的数据进行统计

SQL28 计算用户8月每天的练题数量

题目链接

select 
    day(date) day,
    count(1) question_cnt
from question_practice_detail
where year(date)=2021 and month(date)=8
group by date

关键点：

• 使用year()、month()、day()函数提取日期中的年月日

SQL29 计算用户的平均次日留存率

题目链接

select 
    count(distinct b.device_id, b.date)/count(distinct a.device_id, a.date) tmp
from question_practice_detail a 
left join question_practice_detail b
on datediff(b.date,a.date)=1 and a.device_id=b.device_id

关键点：

• 使用datediff()函数进行日期的加减，计算日期差距

SQL30 统计每种性别的人数

题目链接

select 
    case 
        when profile like "%female" then "female"
        when profile like "%male" then "male"
    end gender, 
    count(1) number
from user_submit
group by gender

SELECT IF(profile LIKE '%female','female','male') gender,COUNT(*) number
FROM user_submit
GROUP BY gender;

SELECT SUBSTRING_INDEX(profile,",",-1) gender,COUNT(*) number
FROM user_submit
GROUP BY gender;

SELECT
    case
    when FIND_IN_SET('male', profile) then 'male'
    when FIND_IN_SET('female', profile) then 'female'
    else '' end as 'gender',
    count(*) number
from user_submit
group by gender

关键点：

• 使用SUBSTRING_INDEX()将字符串按照分隔符拆分，第二个参数是前n个块
• IF(condition,1,0)类似excel的条件函数
• FIND_IN_SET()函数可以判断字符串是否在字符串中
• 其他常用字符串函数：
  • ATE(substr,str)：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；
  • POSITION(substr IN str)：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同；
  • LEFT(str,length)：从左边开始截取str，length是截取的长度；
  • RIGHT(str,length)：从右边开始截取str，length是截取的长度；
  • SUBSTRING(str,n,m)：返回字符串str从第n个字符截取到第m个字符；
  • REPLACE(str,n,m)：将字符串str中的n字符替换成m字符；
  • LENGTH(str)：计算字符串str的长度。
  • TRIM('被删除字段' from 列名)：从字符串中剔除某些字符串

SQL33 找出每个学校GPA最低的同学

题目链接

select a.device_id, a.university, a.gpa
from user_profile a
right join
(
    select university, min(gpa) as gpa
    from user_profile
    group by university
) as b
on a.university=b.university and a.gpa=b.gpa
order by a.university
 

select device_id, university, gpa
from (
    select *,
    row_number() over (partition by university order by gpa) as rn
    from user_profile
) as univ_min
where rn=1
order by university

select device_id,university,gpa
from user_profile
where (university,gpa) in (select university,min(gpa) from user_profile group by university)
order by university

关键点：

• 窗口函数
• 元组式判断是否在内