SQL39道常见题型

已于 2024-07-23 16:00:42 修改
阅读量1.6k 收藏 19
点赞数 47
文章标签： 大数据
于 2024-07-21 17:15:53 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_61077780/article/details/140588045
版权

SQL1 查询所有列

现在运营想要查看用户信息表中所有的数据，请你取出相应结果。

select * from user_profile

结果：

SQL2 查询多列

还是上面那个输入，题目换成：现在运营同学想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据。

select device_id, gender,age,university from user_profile

选择指定的列： SELECT 字段名 FROM 表名;

SQL3 查询结果去重

现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。

两种方式:

1.distinct 关键字

select DISTINCT university FROM user_profile;

select distinct university from user_profiledistinct去重，放在列的前面使用。

2.分组group by 来筛选出去重的结果

当你对某个列使用 GROUP BY 时，数据库会将该列中相同值的行分为一组。对于每一组，只返回一个记录。因此，在对特定列进行 GROUP BY 操作时，相当于对该列进行去重。

select university FROM user_profile group by university;

SQL4 查询结果限制返回行数

现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。

select device_id from  user_profile LIMIT 2;

其他用法：

取按特定顺序排序的前几条记录

SELECT * FROM user_profile ORDER BY registration_date DESC LIMIT 2;

选择中间的任意几行

SELECT * FROM table LIMIT count OFFSET offset;
  • count：要返回的行数。
  • offset：要跳过的行数。
SELECT * FROM table LIMIT 3 OFFSET 2;
  • OFFSET 2：跳过前2行。
  • LIMIT 3：返回接下来的3行。

.检索记录行 6-10

SELECT * FROM table LIMIT 5,5

检索记录行 11-last

SELECT * FROM table LIMIT 10,-1

SQL5 将查询后的列重新命名

SELECT device_id AS user_infos_example FROM user_profile LIMIT 2;

AS可以省略

SELECT device_id user_infos_example FROM user_profile LIMIT 2;

SQL6 查找学校是北大的学生信息

现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。

select device_id,university from user_profile where university='北京大学';
select device_id,university from user_profile where university like '%北京%'

'%北京%' 的意思是匹配所有包含“北京”这个子字符串的字符串，不论它出现在字符串的开头、中间还是结尾。它使用 SQL 中的 LIKE 子句来查找包含特定子字符串的行。

SQL7 查找年龄大于24岁的用户信息

现在运营想要针对24岁以上的用户开展分析，请你取出满足条件的设备ID、性别、年龄、学校。

select device_id,gender,age,university from user_profile where age>24;

严谨起见，可以加上 age is not null 的条件

select device_id,gender,age,university from user_profile where age is not null and age>24;

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

现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄.
1.between 在列值得某与某之间

select device_id,gender,age from user_profile where age between 20 and 23;

2.用and的来连接条件范围

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

SQL9 查找除复旦大学的用户信息

现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据.

select device_id,gender,age,university from user_profile where university <> '复旦大学';
select device_id,gender,age,university from user_profile where university != '复旦大学';

如果你想对查询结果进行去重，确保每个学校和id等信息只出现一次，可以使用 DISTINCT 关键字来实现。DISTINCT 用于返回不同的唯一值。

select DISTINCT device_id,gender,age,university from user_profile where university != '复旦大学';

SQL10 用where过滤空值练习

现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。

select device_id,gender,age,university from user_profile where age is not null;
select device_id,gender,age,university from user_profile where age is not null and age <> "";

 返回所有 age 列值既不为空（NULL）也不等于空字符串 "" 的记录。

select device_id,gender,age,university from user_profile where age is not null and age <> 0;

SQL11 高级操作符练习(1) -AND 

现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研。

select device_id,gender,age,university,gpa from user_profile where gpa >3.5 and gender = "male";

IN 操作符用于匹配多个可能的值，在这里只匹配 'male'，所以 gender = 'male' 和 gender IN ('male') 是等效的。 

select device_id,gender,age,university,gpa from user_profile where gpa >3.5 and gender in('male');

SQL12 高级操作符练习(2）-OR

现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研，请你取出相关数据（使用OR实现）

select device_id,gender,age,university, gpa from user_profile where gpa>3.7 OR university IN('北京大学')
select device_id,gender,age,university, gpa from user_profile where gpa>3.7 OR university ='北京大学';

SQL13 Where in 和Not in

现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。

select device_id,gender,age,university,gpa from user_profile where university in ('北京大学','复旦大学','山东大学');
select device_id,gender,age,university,gpa from user_profile where university='北京大学' or university='复旦大学' or university='山东大学';

SQL14 操作符混合运用

现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据。

select device_id,gender,age,university,gpa from user_profile where gpa>3.5 and university='山东大学' or gpa >3.8 and university='复旦大学';

AND 的优先级高于 OR。这意味着在没有括号的情况下，AND 运算会比 OR 运算先被计算。

SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE (gpa > 3.5 AND university = '山东大学')
   OR (gpa > 3.8 AND university = '复旦大学');

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

现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据。

select device_id,age,university from user_profile where university lIKE('%北京%');

LIKE 的用法

  1. 百分号 (%):

    • 用法：用于匹配零个或多个字符。
    • 示例：
      • LIKE 'A%' 匹配以 'A' 开头的所有记录。
      • LIKE '%A' 匹配以 'A' 结尾的所有记录。
      • LIKE '%A%' 匹配包含 'A' 的所有记录。
      • LIKE 'A%B' 匹配以 'A' 开头，'B' 结尾的记录，中间可以有零个或多个字符。

  2. 下划线 (_):

    • 用法：用于匹配一个单独的字符。
    • 示例：
      • LIKE 'A_' 匹配以 'A' 开头，后跟一个字符的记录。
      • LIKE '_A' 匹配以一个字符开头，后跟 'A' 的记录。
      • LIKE '_A_' 匹配一个字符，后跟 'A' 和一个字符的记录。

  3. 转义字符:

    • 用法：用于在模式中匹配实际的百分号 (%) 或下划线 (_) 字符。这些字符通常被视为通配符，但可以使用转义字符来匹配它们。
    • 示例：
      • LIKE '100\%' ESCAPE '\' 匹配 '100%' 字符串，其中 \ 是转义字符。
      • LIKE 'A\_B' ESCAPE '\' 匹配 'A_B' 字符串，其中 \ 用来转义下划线。

SQL16 查找GPA最高值

运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据。

因最高的gpa，max(gpa)求出最大值

select max(gpa) as gpa from user_profile where university='复旦大学';

因最高的gpa，进行order by gpa desc降序，取第一行的数据即可 

select gpa from user_profile where university='复旦大学' order by gpa desc limit 1;

SQL17 计算男生人数以及平均GPA

现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动

select count(gender) as male_num, avg(gpa) as avg_gpa from user_profile where gender='male';

严谨到小数点, ROUND函数

ROUND(numeric_expression, decimal_places)

  • numeric_expression：要四舍五入的数值表达式。
  • decimal_places：要保留的小数位数。如果省略此参数，默认将四舍五入到最接近的整数。
select count(gender) as male_num,ROUND(avg(gpa),1) as avg_gpa from user_profile where gender='male';

SQL18 分组计算练习题

现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

select gender,university,count(device_id) as user_num , avg(active_days_within_30) as avg_active_day ,avg(question_cnt) as avg_question_cnt from user_profile group by university,gender;
  • 每个学校每种性别：按学校和性别分组：group by gender, university
  • 用户数：count(device_id)
  • 30天内平均活跃天数：avg(active_days_within_30)
  • 平均发帖数量：avg(question_cnt)
  • 表头重命名：as

SQL19 分组过滤练习题

现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

  • 限定条件：平均发贴数低于5或平均回帖数小于20的学校，avg(question_cnt)<5 or avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；
  • 按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此group by university
select university, ROUND(AVG(question_cnt),3) AS avg_question_cnt,ROUND(AVG(answer_cnt),3) AS avg_answer_cnt FROM user_profile group by university having avg_question_cnt <5 or avg_answer_cnt<20;
  • 表头重命名：as
  • 用having不用where

不考虑小数点；

select
    university,
    avg(question_cnt) as avg_question_cnt,
    avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt<5 or avg_answer_cnt<20

SQL20 分组排序练习题

现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。

  • 不同大学：按学校分组group by university
  • 平均发帖数：avg(question_cnt)
  • 升序排序：order by avg_question_cnt
  • 表头重命名：as
select university, avg(question_cnt) as avg_question_cnt from user_profile group by university order by avg_question_cnt;

 HAVING 子句用于在分组后过滤结果，而 ORDER BY 子句用于对最终结果进行排序。如果有过滤条件的话加上having

select university, avg(question_cnt) as avg_question_cnt from user_profile group by university having avg_question_cnt is not null order by avg_question_cnt;

例如只选择平均发帖数量大于一定值的学校

SELECT university, AVG(question_cnt) AS avg_question_cnt
FROM user_profile
GROUP BY university
HAVING AVG(question_cnt) > 10
ORDER BY avg_question_cnt;

SQL21 浙江大学用户题目回答情况

现在运营想要查看所有来自浙江大学的用户题目回答明细情况。

 

 解法一：

select qpd.device_id,qpd.question_id,qpd.result from question_practice_detail as qpd inner join user_profile as up on qpd.device_id=up.device_id where university='浙江大学';

来自浙江大学的用户,学校信息在user_profile表中，答题明细在question_practice_detail表中，故需要使用device_id把两个表连接起来，方法1：join两个表，用inner join，条件是on up.device_id=qpd.device_id and up.university='浙江大学'

ON 子句：在连接操作中指定两个表之间的匹配条件。

连接类型：不同的连接类型（INNER, LEFT, RIGHT, FULL）决定了结果集中包含哪些行。

 解法二：子查询来实现，这个子查询首先筛选出符合条件的 device_id，然后在主查询中使用这些 device_id 来过滤数据。

select device_id,question_id,result from question_practice_detail where device_id in (select device_id from user_profile where university='浙江大学');

子查询从 user_profile 表中筛选出所有 university 为 '浙江大学' 的 device_id。

主查询从 question_practice_detail 表中选择 device_id、question_id 和 result，其中 device_id 在子查询返回的列表中。

SQL22 统计每个学校的答过题的用户的平均答题数

运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。

select
    university,
    count(question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from
    question_practice_detail as qpd
    inner join user_profile as up on qpd.device_id = up.device_id
group by
    university;

select
    up.university,
    count(qpd.question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from
    question_practice_detail as qpd
    inner join user_profile as up on qpd.device_id = up.device_id
group by
    up.university;
select
    up.university,
    count(qpd.question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from
    user_profile as up
    inner join question_practice_detail as qpd on qpd.device_id = up.device_id
group by
    up.university;

SQL23 统计每个学校各难度的用户平均刷题数

运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量

select up.university, qp.difficult_level,count(qpd.question_id)/count(distinct qpd.device_id) as avg_answer_cnt from question_practice_detail as qpd 
left join question_detail as qp
on qp.question_id= qpd.question_id
left join user_profile as up
on up.device_id=qpd.device_id
group by up.university,qp.difficult_level;
  • 每个学校：按学校分组group by university
  • 不同难度：按难度分组group by difficult_level
  • 平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id)
  • 来自上面信息三个表，需要联表，up与qpd用device_id连接，qd与qpd用question_id连接。

SQL24 统计每个用户的平均刷题数

 运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，

  • 链接三个表后，以学校和难度分组
  • 分组完成后用having 过滤
select up.university,qd.difficult_level,count(qpd.question_id)/count(distinct qpd.device_id) as avg_answer_cnt from question_practice_detail as qpd 
left join question_detail as qd
on qd.question_id=qpd.question_id
left join user_profile as up
on up.device_id	=qpd.device_id	
group by up.university,qd.difficult_level
having up.university='山东大学';
select up.university,qd.difficult_level,count(qpd.question_id)/count(distinct qpd.device_id) as avg_answer_cnt
from 
    user_profile as up,
    question_practice_detail as qpd,
    question_detail as qd
where 
    up.university='山东大学' 
    and qpd.device_id=up.device_id 
    and qpd.question_id=qd.question_id
group by qd.difficult_level;

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

现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

注意：用where和or就是查询学校是山东大学或者为男生的信息。where和or一起用有去重作用，和union的作用一样。所以以下语句是错误的：

select device_id,gender,age,gpa from user_profile 
where university='山东大学' or gender='male';
  • 限定条件：学校为山东大学或者性别为男性的用户：university='山东大学', gender='male'；
  • 分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行，要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重。
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';

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

现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

本题注意：age为null 也记为 25岁以下。

CASE 表达式在 SQL 中用于进行条件判断，并返回相应的结果。它有两种形式：简单 CASE 和搜索 CASE。

简单 CASE 表达式

简单 CASE 表达式用于比较一个表达式与一组简单的值，并根据匹配的结果返回相应的值。

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE resultN
END

搜索 CASE 表达式

搜索 CASE 表达式用于基于布尔条件进行判断，并返回相应的结果。

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

这个题就是搜索 CASE 表达式

select case 
    when age<25 or age is null then '25岁以下' 
    when  age>=25 then '25岁及以上' end age_cut,count(*)number 
    from user_profile group by age_cut

as 可以省略，不省略如下：

select case 
    when age<25 or age is null then '25岁以下' 
    when  age>=25 then '25岁及以上' end as age_cut,count(*) as number 
    from user_profile group by age_cut

SQL27 查看不同年龄段的用户明细

现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）

  • 划分年龄段：数值条件判断，可以用多重if，不过更方便的是用case when [expr] then [result1]...else [default] end
select device_id,gender, 
case when age<20 then '20岁以下' 
     when age>=20 and age<=24 then '20-24岁'
     when age >=25 then '25岁及以上'
     else '其他'
     end age_cut 
from user_profile

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

现在运营想要计算出2021年8月每天用户练习题目的数量.

  • 2021年8月，写法有很多种，比如用year/month函数的year(date)=2021 and month(date)=8，比如用date_format函数的date_format(date, "%Y-%m")="202108"
  • 每天：按天分组group by date
  • 题目数量：count(question_id)
  • 表头重命名：as
  • 输出示例中每天的字段只取了几号，要去掉年月，用day函数即可
  • DAY(date) AS day 提取了每个 order_date 中的“日”部分，并将其列名命名为 day。
select day(date) as day,count(question_id) as question_cnt
from question_practice_detail where month(date)=8 and year(date)=2021
group by date;

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

现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率.

SQL30 统计每种性别的人数--字符串

现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，

-- 提取 profile 列中最后一个逗号分隔的部分作为性别

-- 计算每种性别的用户数量

-- 按性别分组

第一种解法SUBSTRING_INDEX

select substring_index(profile,",",-1) as gender ,count(*) as number from user_submit group by gender;

substring_index（str,delim,count）

str:要处理的字符串

delim:分隔符

count:技术

例子：str=www.wikibt.com

substring_index(str,'.',1) 结果是www

substring_index(str,'.',2)  结果是www.wikibt

substring_index(str,'.',-2)  结果是wikibt.com

也就是说：如果count是正数，就是从左往右数到第count个分隔符，然后取到分隔符左边的所有字符，如果count是负数，例如-2，就是从右往左数，数到第二个分隔符，取到右边的所有字符。

如果我需要取到中间的字符例如wikibt： substring_index（substring_index(str,'.',-2) ,'.',1）

第二种解法when .... then....end ...

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

这种适合要去的字符串在整个字符串的结尾。

LIKE 运算符的使用:

  • LIKE '%,male' 和 LIKE '%,female' 用于匹配以 ,male 或 ,female 结尾的 profile 字符串。

第三种解法IF 函数

IF 函数来根据 profile 列的值确定性别，并统计每种性别的用户数量。

COUNT(*) AS number：计算每个性别组的所有记录数，包括 profile 列中值为 NULL 的记录（如果有的话）。

select
    if (profile like '%female', 'female', 'male') as gender,
    count(device_id) as number
from
    user_submit
group by
    gender;

IF(profile LIKE '%female', 'female', 'male'):

  • 该函数会检查 profile 列的每个值。如果 profile 以 female 结尾，则 gender 列的值为 'female'；否则为 'male'。
  • '%female' 的意思是查找任何以 "female" 结尾的字符串。

COUNT(device_id) AS number：计算每个性别组中 device_id 列非 NULL 值的记录数。如果 device_id 列中有 NULL 值，这些记录不会被计入总数。 

select
    if (profile like '%female', 'female', 'male') as gender,
    count(*) as number
from
    user_submit
group by
    gender;

SQL31 提取博客URL中的用户名

对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

 第一种方法substring_index

select device_id ,
substring_index(blog_url,'/',-1) as user_name 
from user_submit;

第二种方法 REPLACE函数

  • REPLACE(blog_url, 'http:/url/', '')：
    • REPLACE 是一个字符串函数，用于将字符串中的某个部分替换为另一个字符串。
    • 第一个参数 blog_url：这是待处理的列。
    • 第二个参数 'http:/url/'：这是要被替换的子字符串。
    • 第三个参数 ''：这是替换后的新字符串，这里是一个空字符串，意味着删除匹配的子字符串。
select device_id ,
replace(blog_url,'http:/url/','') as user_name 
from user_submit;

第三种方法删除法 trim 函数

TRIM([[LEADING | TRAILING | BOTH] [remstr] FROM] str)
  • LEADING：删除字符串 str 开头的 remstr 字符串。
  • TRAILING：删除字符串 str 结尾的 remstr 字符串。
  • BOTH（默认）：删除字符串 str 开头和结尾的 remstr 字符串。
  • remstr：要删除的字符或字符串。
  • str：待处理的字符串。

 删除开头的子字符串

select device_id ,
TRIM(LEADING 'http:/url/' FROM blog_url) as user_name 
from user_submit;

 删除首尾的子字符串

select device_id ,
TRIM('http:/url/' FROM blog_url) as user_name
from user_submit;

第四种方法SUBSTR 函数或 SUBSTRING 函数

select device_id ,
substr(blog_url,11,length(blog_url)-10) as user_name
from user_submit;

SUBSTR(blog_url, 11, LENGTH(blog_url) - 10)：

  • blog_url 是要处理的字符串列。
  • 11 是起始位置，从字符串的第 11 个字符开始（字符串索引从 1 开始）。
  • LENGTH(blog_url) - 10 提取长度为字符串总长度减去 10。有助于忽略 blog_url 列中的前 10 个字符，提取其余部分。

 SQL32 截取出年龄

现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果

select substring_index(substring_index(profile,',',-2),',',1) as age,
count(*) as number 
from user_submit group by age;

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

现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。一个学校有多个学生。

SELECT 
    device_id,
    university,
    MIN(gpa) AS gpa
FROM 
    user_profile
GROUP BY 
    university
ORDER BY 
    university;

 这种方法会导致出错，因为查询中的 device_id 列与聚合函数 MIN(gpa) 之间存在不一致性。

在这种查询中，device_id 是一个非聚合列，而对 gpa 使用了聚合函数 MIN(gpa)。SQL 无法确定在同一组内 device_id 应该如何处理，因为 device_id 在每个组中可能有多个不同的值。

选择的列与 GROUP BY 不一致：

  • GROUP BY university 将数据按学校分组。
  • MIN(gpa) AS gpa 会计算每个学校的最低 GPA。
  • device_id 列没有被 GROUP BY 处理或聚合，因此 SQL 不知道如何将 device_id 和最低 GPA 对应起来。也就是说根据学校分组之后，每组里面只有一个最小gpa,但是有多个device_id，不知道对于每个学校的最低 GPA 应该选择哪一个 device_id。

正确的方法

第一种子查询 

SELECT
    a.device_id,
    a.university,
    a.gpa
FROM
    user_profile as a
WHERE
    a.gpa in (
        SELECT
            MIN(b.gpa)
        FROM
            user_profile as b
        WHERE
            a.university = b.university
    )
ORDER BY
    a.university;

 外部查询（主查询）

  • SELECT a.device_id, a.university, a.gpa FROM user_profile a
  • 从 user_profile 表中选择列 device_id、university 和 gpa。

子查询：

  • SELECT MIN(b.gpa) FROM user_profile b WHERE a.university = b.university
  • 这个子查询的作用是：
    • 从 user_profile 表（别名为 b）中选择 MIN(b.gpa)。
    • MIN(b.gpa) 返回在同一个学校中所有学生的最低 GPA。
    • 子查询中的 WHERE a.university = b.university 是连接条件，它确保子查询只计算当前外部查询正在处理的学校的最低 GPA。

 第二种窗口函数

SELECT device_id, university, gpa
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY university ORDER BY gpa ASC) AS rn
    FROM user_profile
) AS univ_min
WHERE rn = 1
ORDER BY university;

内部查询 

  • ROW_NUMBER() OVER (PARTITION BY university ORDER BY gpa ASC)：
    • PARTITION BY university：为每个学校分组（即为每个学校的数据分区）。
    • ORDER BY gpa ASC：在每个学校的数据分组中，按 GPA 升序排序。
    • ROW_NUMBER()：为每个分组内的记录分配一个唯一的行号（排名），GPA 最低的记录的行号为 1。
  • 结果是一个包含所有原始字段以及一个新字段 rn 的结果集，其中 rn 表示每个学校内的 GPA 排名。

外部查询

SELECT device_id, university, gpa
FROM ( ... ) AS univ_min
WHERE rn = 1
ORDER BY university;
  • WHERE rn = 1：
    • 从内部查询结果中筛选出排名为 1 的记录，即每个学校的最低 GPA 记录。
  • ORDER BY university：
    • 对最终结果按学校排序，以便按学校顺序展示最低 GPA 的记录。

 

 SQL34 统计复旦用户8月练题情况

现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

 

select up.device_id,up.university,
       coalesce(sum(qpd.device_id is not null),0) as question_cnt,
       coalesce(sum(qpd.result ='right'),0) as right_question_cnt

from user_profile as up

left join question_practice_detail as qpd 
on up.device_id=qpd.device_id and month(qpd.date)=8

where up.university='复旦大学'
group by up.university,up.device_id

 

SQL35 浙大不同难度题目的正确率

现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

 

 

 

select qd.difficult_level ,
ROUND(sum(case when qpd.result='right' then 1 else 0 end)/count(qpd.device_id),4) as correct_rate
from user_profile as up
inner join question_practice_detail as qpd on up.device_id=qpd.device_id
inner join  question_detail as qd on qd.question_id=qpd.question_id
where up.university='浙江大学'
group by qd.difficult_level
order by correct_rate;
select qd.difficult_level,
sum(if(qpd.result='right',1,0))/count(qpd.device_id) as correct_rate

from question_practice_detail as qpd 
left join user_profile as up on up.device_id=qpd.device_id
left join question_detail as qd on qd.question_id=qpd.question_id
where up.university='浙江大学'
group by qd.difficult_level
order by correct_rate asc;

 

SQL36 查找后排序

现在运营想要取出用户信息表中的用户年龄，请取出相应数据，并按照年龄升序排序。

select 	device_id,age from user_profile order by age;

 

SQL37 查找后多列排序

现在运营想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出，请取出相应数据。

order by +属性+后面不写默认为升序

select device_id,gpa,age from user_profile order by gpa,age;
select device_id,gpa,age from user_profile order by gpa asc,age asc ;
select device_id,gpa,age from user_profile order by gpa,age asc ;

SQL38 查找后降序排列

现在运营想要取出用户信息表中对应的数据，并先按照gpa、年龄降序排序输出，请取出相应数据。

select device_id,gpa,age from user_profile order by gpa desc,age desc;

SQL39 21年8月份练题总数

现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数，请取出相应结果.

select count(distinct device_id) as did_cnt  ,count(question_id) as question_cnt from question_practice_detail where month(date)=8;
select count(distinct device_id) as did_cnt  ,count(question_id) as question_cnt from question_practice_detail where year(date)='2021' and month(date)='08';
select count(distinct device_id) as did_cnt  ,count(question_id) as question_cnt from question_practice_detail where date like "2021-08%";
select count(distinct device_id) as did_cnt  ,count(question_id) as question_cnt from question_practice_detail where date_format(date,'%Y-%m')='2021-08';

KristinSun
关注
  • 47
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQL Server高可用的常见问题
12-14
1、难道SQL Server在高可用上面显得这么的无力吗？ 　回答：不是的，其实SQL Server很给力。 　2、为什么很多人总是抱怨SQL Server中高可用很难实现呢？ 　这里从几个方面来说。 　首先，所接触到的很多的...
sql常见问题
03-24
在实际使用过程中，用户可能会遇到各种问题，本篇将基于提供的资料，详细讲解两个常见问题及其解决方案，针对的是MySQL和SQL Server。 首先，我们来看MySQL的重装问题。在尝试重新安装MySQL时，如果系统提示输入...
SQL面试常见问题总结指南
全栈川川
11-10 1351
SQL面试问题总结
sql常见问题解决
一条快死的鱼
10-12 1484
sql语句中返回时间格式（为了方便java代码中不用处理时间） DATE_FORMAT(字段名, ‘%Y-%m-%d %H:%i:%s’) AS 别名, %M 月名字(January……December) %W 星期名字(Sunday……Saturday) %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） %Y 年, 数字, 4 位 %y 年, 数字, 2 位 %a 缩写的星期名字(Sun……Sat) %d 月份中的天数, 数字(00……31) %e 月份中的天数, 数字(0……3
flink sql常见问题
weixin_44624060的博客
08-11 1701
flink sql
sql注入详解
good good study
05-05 20万+
SQL注入（SQL Injection）是一种常见的Web安全漏洞，主要形成的原因是在数据交互中，前端的数据传入到后台处理时，没有做严格的判断，导致其传入的“数据”拼接到SQL语句中后，被当作SQL语句的一部分执行。 从而导致数据库受损（被脱库、被删除、甚至整个服务器权限陷）。 即：注入产生的原因是后台服务器接收相关参数未经过滤直接带入数据库查询...
面试 SQL整理 常见的SQL面试题：经典50题
热门推荐
sh_c1314
09-13 26万+
目录 ​ SQL基础知识整理: 常见的SQL面试题：经典50题 三、50道面试题 2.汇总统计分组分析 3.复杂查询 sql面试题：topN问题 4.多表查询 【面试题类型总结】这类题目属于行列如何互换，解题思路如下： 其他面试题: SQL基础知识整理: select 查询结果 如:[学号,平均成绩：组函数avg(成绩)]from 从哪张表中查找数...
SQL server常见问题
weixin_44196899的博客
10-18 2万+
SQL 安装失败
CTF中SQL注入常见题型整理
test
10-10 11万+
前言 正文 无过滤带回显的情况 可能使用到的工具：firefox、hackbar for firfox、sqlmap for linux 手工注入 bugku的环境 在这一环境中的主要是通过post方式传入一个参数id来查询数据库内容。 首先判断sql语句闭合方式 当在id的值后面加上'时，界面无回显，可以判断后端的sql语句应该是 select xxxx from xxxx where...
SQL常见题目
Michael_Jay的博客
01-01 1578
汇总了牛客中SQL必知必会的错题
SQL常见问题总结
疯流人物
11-10 1825
4、数值类型的字段值为NULL时不能进行计算，可以通过ISNULL(字段，0)解决。1、类型 varchar 发生算术溢出错误，值 = 2162400.000000。3、提示某字段无效是一般是字段的归属表指定不明确，如果提示表无效一般是表不存。2、从数据类型varchar转换为numeric时出错。可能原因：转换类型长度不够。可能原因：字段有为空的值。...
sql2000安装常见问题
03-28
sql2000安装常见问题 将数据库安装光盘插入光驱后，出现以下提示框。请选择 "安装 SQL Server 2000 组件"，出现下一个页面后，选择 "安装数据库服务器" 。 　在 "安装选择" 窗口，选择 "创建新的SQL Server实例......
10种Java开发者编写SQL语句时常见错误
09-03
Java开发者在编写SQL语句时，常常会遇到一些由于对SQL特性和面向对象编程思维的不适应而导致的常见错误。以下是对这些错误的详细解析： 1. **误解NULL的含义**： SQL中的NULL并不等同于Java中的null，它们在逻辑上...
SQL Server常见问题及解决方法分享
09-09
总的来说，理解和解决SQL Server的常见问题需要对数据库原理、SQL语法以及SQL Server的特性和管理有深入的了解。通过不断学习和实践，我们可以更有效地应对这些问题，提高SQL Server的稳定性和性能。
RabbitMQ-消息队列之routing使用
龙哥·三年风水从2011年开始做IT工作，从ps画图到前端开发->后端开发->框架开发->业务架构设计及开发->业务需求整理、开发->技术经理->技术总监
08-17 438
RabbitMQ-消息队列之routing使用
prometheus
2201_75912662的博客
08-18 981
无人驾驶车辆运行中要记录的经度，纬度，速度，方向，旁边物体的距离等等。大都为插入操作较多且无更新的需求，插入数据多，每秒钟插入数据可到达千万甚至是上亿条。分析过去时序数据可以做成多纬度报表，揭示其趋势性、规律性、异常性。分析时序数据趋势可以做大数据分析，机器学习，实现预测和预警。按照时间顺序记录系统、设备状态变化的数据被称为时序数据。不依赖分布式存储，单个服务器节点是自主的。数据带有时间属性，且数据量随着时间递增。某一个地区的各车辆的行驶轨迹数据。模型拉去时间序列数据。可以查看到监控的数据。
每日一问：Kafka消息丢失与堆积问题分析（简化版）
upgrador的博客
08-17 595
在本篇博客中，我们将深入探讨 Kafka 中常见的两大问题：消息丢失和消息堆积。首先，我们将简要介绍 Kafka 的基本工作原理，随后分别分析消息丢失和堆积的原因，并提供针对性的解决方案。
数字（智）化采购系统优点_亮点_应用场景
最新发布
xinyuan_123456的博客
08-19 384
数字化采购系统研发商，系统融合云原生、微服务、大数据、人工智能、物联网等先进技术，构建业务中台、数据中台、AI中台三大核心基座，以采购为切入点，结合供应链管理理念，为招采供应链提供交易、监管、数据、服务、运营为一体的全流程数智化解决方案
2024新型数字政府综合解决方案（八）
绿茵场的激情
08-16 254
新型数字政府综合解决方案结合人工智能、大数据、区块链与云计算等先进技术，旨在构建一个智能、高效、透明的政务服务体系，通过全面整合各部门的信息资源，实现数据的实时共享与高效管理，从而大幅提升政府应对复杂社会问题的能力；此外，区块链技术的引入，确保了数据的安全性和透明性，强化了公众对政府信息的信任，在提高政府服务质量的同时，促使市民积极参与社会治理，最终实现数字化转型与可持续发展的战略目标。
Oracle SQL优化技巧与常见问题解析
"这份资料是关于SQL优化的PPT文档，由系统支撑部董建忠分享，内容包括SQL优化的概述、需要优化的情况、常见问题以及如何发现问题的多种方法。文档详细介绍了引发优化需求的各种因素，如等待事件、资源消耗、运行效率...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值