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

SQL3 查询结果去重

题目连接

1
2
select distinct university 
from user_profile

关键点:

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

SQL4 查询结果限制返回行数

题目链接

1
2
3
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 查找某个年龄段的用户信息

题目链接

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

关键点:

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

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

题目链接

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

关键点:

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

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

题目链接

1
2
3
4
5
6
7
8
9
10
11
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岁以上和以下的用户数量

题目链接

1
2
3
4
5
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月每天的练题数量

题目链接

1
2
3
4
5
6
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 计算用户的平均次日留存率

题目链接

1
2
3
4
5
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 统计每种性别的人数

题目链接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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最低的同学

题目链接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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

关键点:

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