牛客网sql练习心得

一起逃去看海吧

已于 2024-03-28 10:38:43 修改

阅读量330

点赞数 8

CC 4.0 BY-SA版权

分类专栏： sql 文章标签： sql mysql 数据库

于 2024-03-27 22:47:26 首次发布

本文链接：https://blog.youkuaiyun.com/PHPneweer/article/details/137089837

sql 专栏收录该内容

1 篇文章

订阅专栏

文章详细介绍了SQL中的notin、in、notnull、wherein以及复合条件查询，包括子查询、GROUPBY的多列分组、UNION和COUNT的使用，以及如何计算平均值和完成率。此外，还涉及MySQL特定函数如ROUND和DATE_FORMAT的运用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

not in用法

1、

select device_id,gender,age,university

from user_profile

where university not in('复旦大学')

某个不再某个之中

2、子查询

select * from x where not in(查询语句select)

不在查询之中

可以用逗号并列

3、not null查询不为空

select device_id,gender,age,university

from user_profile

where age is not Null

4、where in

select device_id,gender,age,university,gpa

from user_profile

where university in ('北京大学','复旦大学','山东大学')

在这三个大学的有

5、条件复合使用

select device_id,gender,age,university,gpa

from user_profile

where (gpa>3.5 and university='山东大学')

or (gpa>3.8 and university='复旦大学')

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

select a.device_id,question_id,result

from question_practice_detail a

JOIN user_profile b on a.device_id=b.device_id

where b.university='浙江大学'

order by device_id DESC

7、

/*
分析思路
select 查询结果 [university,'每个人平均答题数量':问题数/设备数(一个设备对应多个题，要去重)]
from 从哪张表中查找数据[两张表联结]
where 查询条件 [无]
group by 分组条件 [university]
*/

-- avg_answer_cnt = count(question_id)/count(device_id)  
select university,(count(question_id)/count(distinct(q.device_id))) 
as avg_answer_cnt 
from user_profile u 
join question_practice_detail q on u.device_id = q.device_id
group by university;

8、
运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据

select a.university,c.difficult_level,count(a.answer_cnt)/count(distinct(a.device_id)) as avg_answer_cnt
from user_profile a
join question_practice_detail b on a.device_id=b.device_id
join question_detail c on c.question_id=b.question_id
group by a.university, c.difficult_level;

在你的查询中，`GROUP BY` 子句需要指定多个列的原因是为了按照这些列的组合来进行分组计算。在这里，你想要计算每个学校（university）和题目难度级别（difficult_level）的平均答题次数。因此，你需要同时按照这两个列的组合来分组数据，以便得出正确的平均值。

如果你只按照一个列进行分组，那么计算的平均答题次数将是整个学校或难度级别的总体平均值，而不是每个学校和难度级别的平均值。通过同时指定多个列进行分组，可以确保你得到每个学校和难度级别的独立平均值。

其实这里的不去重表示：只要满足一个条件就被筛选出来，但总会存在一个人满足了两个条件只筛选一次。这里的坑时使用or，因为or自带去重，而union等价于or，但union all 可以不去重，所以本体考察or与union的细节使用。
select device_id,gender,age,gpa from user_profile where gender='male' 
union 
select device_id,gender,age,gpa from user_profile where gender='male'

if(1>2,2,3)
->3

if(1<2,'yes','no')
->yes

select age_cut,count(device_id)as number 
from(Select if(age>=25,'25岁及以上','25岁以下' )as age_cut,device_id 
        From user_profile
    )u2
Group by age_cut
根据示例，你的查询应返回以下结果：
age_cut	   number
25岁以下     4
25岁及以上   3

去掉一个最大值和一个最小值

SELECT ei.tag,
       ei.difficulty,
       ROUND((SUM(er.score)-MIN(er.score)-MAX(er.score)) / (COUNT(er.score)-2),1) AS clip_avg_score
FROM examination_info ei,exam_record er
WHERE ei.exam_id = er.exam_id
  AND ei.tag = "SQL"
  AND ei.difficulty = "hard"; 
/** 
MYSQL 的 ROUND()函数
ROUND(X),返回参数 X 最近似的整数
ROUND(X,D),返回 X ,其值保留到小数点后 D 位,第 D 位的保留方式为四舍五入
**/

有一个试卷作答记录表exam_record，请从中统计出总作答次数total_pv、试卷已完成作答数complete_pv、已完成的试卷数complete_exam_cnt。
示例数据 exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
1
select
    count(*) as total_pv,
    count(score) as complete_pv,
    count(distinct case when score is null then null else exam_id end) as complete_exam_cnt
from 
    exam_record
2

select count(*) as total_pv,
 
count(score) as complete_pv,
 
count(distinct exam_id,score IS NOT NULL or null) as complete_exam_cnt
 
from exam_record

count中是可以加条件的、

请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。
示例数据 exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

with t1 as(
    SELECT er.*,ei.tag
    FROM exam_record er INNER JOIN examination_info ei
    ON er.exam_id = ei.exam_id
    WHERE tag = "SQL" AND submit_time is not null
)
 
# 子查询查询SQL的平均分
SELECT min(score)
FROM t1
WHERE score >= (SELECT avg(score)
               FROM t1)



//学会了 with as用法

操作年月日date

DATE_FORMAT()函数接受两个参数：

date：是要格式化的有效日期值
format：是由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比字符(%)

select date_format(start_time,'%Y%M')
from exam_record
当M是大写的M时则是月份对应的英文单词
当M是小写的m时则是月份对应的数字