接上期

最新推荐文章于 2023-09-07 13:30:56 发布
最新推荐文章于 2023-09-07 13:30:56 发布
阅读量181 收藏
点赞数
分类专栏： 数据分析 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Saropetry/article/details/106661849
版权

11、求每门课程的学生人数及平均分，输出课程编号、对应学生人数、平均分

SELECT
    course_id,
    count( DISTINCT student_id ) AS student_count,
    avg( score ) AS avg_score 
FROM
    score 
GROUP BY
    course_id

12、求每门课程的学生人数及平均分，输出课程名称、对应学生人数、平均分
方法一

SELECT
    co.NAME,
    co.course_id,
    count( DISTINCT sc.student_id ) AS student_count,
    avg( sc.score ) AS avg_score 
FROM
    score sc
    JOIN course co ON sc.course_id = co.course_id 
GROUP BY
    co.NAME,
    co.course_id


方法二
select a.name
          ,a.course_id
      ,b.student_count
      ,b.avg_score
  from 
    (
        select *
          from course
    ) a
join
    (
        select course_id
              ,count(*) as student_count
                            ,avg(score) as avg_score
          from score
        group by course_id

    ) b
  on a.course_id = b.course_id

13、查询同时学习" 01 "课程和" 02 "课程的学生编号及01和02课程分数

方法一：列存储
SELECT 
student_id,
score,
course_id
from score
where course_id in ('01','02')

方法二：行存储，自关联
select a.student_id
      ,a.score as score_01
      ,b.score as score_02
  from 
    (
      select *
        from score
        where course_id = '01'
    ) a
join
    (
      select *
        from score
        where course_id = '02'
    ) b
  on a.student_id = b.student_id;

14、查询" 01 "课程比" 02 "课程成绩高的学生编号及01和02课程分数

SELECT
    a.student_id,
    a.score AS score_01,
    b.score AS score_02 
FROM
    ( SELECT * FROM score WHERE course_id = '01' ) a
    JOIN ( SELECT * FROM score WHERE course_id = '02' ) b ON a.student_id = b.student_id 
WHERE
    a.score > b.score

select a.student_id
      ,a.score as score_01
      ,b.score as score_02
  from 
    (
      select *
        from score
        where course_id = '01'
    ) a
join
    (
      select *
        from score
        where course_id = '02'
    ) b
  on a.student_id = b.student_id
where a.score > b.score

15、查询" 01 "课程比" 02 "课程成绩高的学生姓名及01和02课程分数

写法一

select c.name
      ,a.score as score_01
      ,b.score as score_02
  from 
    (
      select *
        from score
        where course_id = '01'
    ) a
join
    (
      select *
        from score
        where course_id = '02'
    ) b
  on a.student_id = b.student_id
join (
            select * 
                from student
        ) c
    on a.student_id = c.student_id
where a.score > b.score

写法2：

select name
      ,score_01
      ,score_02
  from
    (
        select *
          from student
    ) a
join      
    (
      select a.student_id
            ,a.score as score_01
            ,b.score as score_02
        from 
          (
            select *
              from score
              where course_id = '01'
          ) a
      join
          (
            select *
              from score
              where course_id = '02'
          ) b
        on a.student_id = b.student_id
      where a.score > b.score
    ) b
  on a.student_id=b.student_id

  • 连续三道题都是有关联的，前两道题给这道题做铺垫，所以当我们拿到一个向这种比较复杂的需求时，可以进行拆分需求，先拆分成1、在做2，最后达到完整的需求。

 16、查询选择了 "01"课程但没选择 "02"课程的学生姓名(同一个字段，取不同值时，用left join )

错误写法：
SELECT
    a.NAME,
    b.course_id 
FROM
    student a
    JOIN score b ON a.student_id = b.student_id 
WHERE
    b.course_id = '01' 
    AND b.course_id <> '02';
----"b.course_id <> '02'" 该条件不起作用
 

写法二：

  1. -- 找出选择了 "01"课程但没选择 "02"课程的学生编号
select a.student_id
  from 
    (
      select *
        from score
        where course_id = '01'
    ) a
left outer join
    (
      select *
        from score
        where course_id = '02'
    ) b
  on a.student_id = b.student_id
where b.student_id is null

    -- 拿着学生编号关联学生表找到姓名
select name
  from 
    (
        select a.student_id as student_id
          from 
            (
              select *
                from score
                where course_id = '01'
            ) a
        left outer join
            (
              select *
                from score
                where course_id = '02'
            ) b
          on a.student_id = b.student_id
          where b.student_id is null
    ) a
join 
    (
        select *
          from student
    ) b
  on a.student_id = b.student_id
  • 首先我们先找出选择了 "01"课程但没选择 "02"课程的学生编号，再拿着学生编号关联学生表找到姓名即可

17、查询学过 '张三' 老师课程的所有同学姓名、生日、性别

写法一
SELECT
    g.* 
FROM
    (
    SELECT
        e.student_id 
    FROM
        (
        SELECT
            b.course_id,
            c.teacher_id,
            c.NAME 
        FROM
            course b
            JOIN ( SELECT teacher_id, NAME FROM teacher WHERE NAME = '张三' ) c ON b.teacher_id = c.teacher_id 
        ) d
        JOIN ( SELECT student_id, course_id FROM score ) e ON d.course_id = e.course_id 
    ) f
    JOIN ( SELECT * FROM student ) g ON f.student_id = g.student_id
    
写法二
select b.*
  from 
    (
        select student_id
          from 
            (
                select course_id
                  from 
                    (
                        select teacher_id
                          from teacher
                        where name='张三'
                    ) a
                join
                    (
                        select course_id
                              ,teacher_id
                          from course
                    ) b
                  on a.teacher_id = b.teacher_id
            ) a
        join 
            (
                select student_id
                      ,course_id
                  from score
            ) b
          on a.course_id = b.course_id
        group by student_id
    ) a
join 
    (
        select *
          from student
    ) b
  on a.student_id = b.student_id

找出 '张三' 老师的教师编号
找出 '张三' 老师所有教授的课程编号
找出这些课程对应的学生编号
根据学生编号找到对应的学生信息
解题思路
根据上述四个步骤，依次书写 SQL

18、查询同时学习 "01"、"02"课程学生的学生编号以及"01"和"02"课程成绩
SELECT
    a.student_id,
    a.score AS score_01,
    b.score AS score_02 
FROM
( SELECT * FROM score WHERE course_id = '01' ) a
JOIN ( SELECT * FROM score WHERE course_id = '02' ) b ON a.student_id = b.student_id

select a.student_id as student_id
      ,a.score as score_01
      ,b.score as score_02
  from 
    (
        select *
          from score
        where course_id = '01'
    ) a
join
    (
      select *
        from score
        where course_id = '02'
    ) b
  on a.student_id = b.student_id;

19、查询学习 "01"课程但没有学习 "02"课程学生的学生编号以及"01"课程成绩

select a.student_id as student_id
      ,a.score as score_01
      ,b.score as score_02
  from 
    (
        select *
          from score
        where course_id = '01'
    ) a
left join
    (
      select *
        from score
        where course_id = '02'
    ) b
  on a.student_id = b.student_id
    where b.course_id is null;


select a.student_id as student_id
      ,a.score as score_01
  from 
    (
        select *
          from score
        where course_id = '01'
    ) a
left outer join
    (
      select *
        from score
        where course_id = '02'
    ) b
  on a.student_id = b.student_id
where b.student_id is null

20、查询学习 "02"课程但没有学习 "01"课程学生的学生编号以及"02"课程成绩
select b.student_id as student_id
      ,a.score as score_01
      ,b.score as score_02
  from 
    (
        select *
          from score
        where course_id = '01'
    ) a
right join
    (
      select *
        from score
        where course_id = '02'
    ) b
  on a.student_id = b.student_id
    where a.course_id is null;


select b.student_id as student_id
      ,b.score as score_02
  from 
    (
        select *
          from score
        where course_id = '01'
    ) a
right outer join
    (
      select *
        from score
        where course_id = '02'
    ) b
  on a.student_id = b.student_id
where a.student_id is null

21、查询选课的同学的学生姓名、选课总数、所有课程的成绩总和、课程平均分

SELECT
    a.NAME,
    course_num,
    score_sum,
    score_avg 
FROM
    ( SELECT student_id, NAME FROM student ) a
    JOIN (
    SELECT
        student_id,
        count( course_id ) AS course_num,
        sum( score ) AS score_sum,
        round( avg( score ), 2 ) AS score_avg 
    FROM
        score 
    GROUP BY
        student_id 
    ) b ON a.student_id = b.student_id 
ORDER BY
    course_num DESC,
    score_sum DESC
                

select a.name as name
      ,course_num
      ,score_sum
      ,score_avg
  from
    (
        select student_id
              ,name
          from student
    ) a
join
    (
        select student_id
              ,count(course_id) as course_num
              ,sum(score) as score_sum
              ,avg(score) as score_avg
           from score
           group by student_id
    ) b
  on a.student_id=b.student_id
order by course_num desc 
        ,score_sum desc

22、查询考试平均分大于 60 分同学的学生姓名、选课总数、所有课程的成绩总和、课程平均分，按照科目数排降序、科目数相同按照分数排降序

select a.name as name
      ,course_num
      ,score_sum
      ,score_avg
  from
    (
        select student_id
              ,name
          from student
    ) a
join
    (
        select student_id
              ,count(course_id) as course_num
              ,sum(score) as score_sum
              ,avg(score) as score_avg
           from score
           group by student_id
                     having avg(score) > 60
    ) b
  on a.student_id=b.student_id
order by course_num desc 
        ,score_sum desc


select a.name as name
      ,course_num
      ,score_sum
      ,score_avg
  from
    (
        select student_id
              ,name
          from student
    ) a
join
    (
        select student_id
              ,count(course_id) as course_num
              ,sum(score) as score_sum
              ,avg(score) as score_avg
           from score
        group by student_id
    ) b
  on a.student_id=b.student_id
where score_avg > 60
order by course_num desc 
        ,score_sum desc

23、检索 "01" 课程分数小于 60 分的学生信息及"01" 课程分数，按照分数排降序

select
b.*
,a.score_01
from 
( select 
student_id
,score as score_01
from score
where course_id='01'
) a 
left join
( SELECT
*
from student
) b
on a.student_id = b.student_id
where a.score_01 < 60
order by a.score_01 desc


select a.student_id as student_id
      ,name
      ,birthday
      ,sex
      ,score
  from 
    (
        select *
          from student
    ) a
join 
    (
        select student_id
              ,score
          from score
        where course_id='01'
            and score < 60
    ) b
  on a.student_id = b.student_id
order by score desc

24、查询两门及其以上不及格课程的同学的姓名及其平均成绩
 写法一
SELECT
    b.NAME,
    a.score_avg 
FROM
    ( SELECT student_id, avg( CASE WHEN score < 60 THEN score ELSE NULL END ) AS score_avg FROM score GROUP BY student_id ) a
    JOIN ( SELECT student_id, NAME FROM student ) b ON a.student_id = b.student_id 
WHERE
    a.score_avg IS NOT NULL

写法二
select name
      ,score_avg
  from 
    (
        select *
          from student
    ) a
join
    (
        select student_id
              ,score_avg
          from 
            (
                select student_id
                      ,avg(score) as score_avg
                      ,count(case when score < 60 then 1 end) as fail_count
                  from score
                group by student_id
            ) a
        where fail_count > 1 
    ) b
  on a.student_id = b.student_id
;

Saropetry
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
上期技术最新API接口文件
12-18
这是上期技术CTP开发的交易接口，最新的2012-5-30版本
PowerBasic版上海期货所接口技术文档+接口调用解决方法
09-30
这是一个采用PB语言编写调用上期接口的技术文档，愿与PB爱好者分享交流。
上期技术的 CTP 连接程序
10-29
QT 下的 上期技术的 CTP 客户端程序。之前网上的 是 VS 下的， 我花了点时间重写了 QT 下的。更详细的版本只有邮件联系。
上期CTP行情交易接口.Net封装 20121028
10-29
上期技术CTP行情及交易接口.Net封装 本次更新到CTP官方库20120530版本，增加了Multiple AppDomain支持
上期技术CTP行情交易接口.Net封装完整版
04-24
上期技术CTP行情交易接口.Net封装完整版 CTP.dll 将非托管C++库转换为托管库，供.Net程序调用。包括行情接口和交易接口。 Struct.h头文件修改自海风版C#的Struct.cs文件，非常感谢！ CSharpMdTest C#行情接口测试...
mysql判断某个字符串是否包含某个字符串的3种方法查询结果对比
热门推荐
Saropetry的博客
06-02 4万+
总结下在MySQL里判断某个字符串是否包含某个字符串的3种方法。 先举个简单的场景，比如要查询user表中爱好中有yanggb的记录。 方法1：使用通配符%。 通配符也就是模糊匹配，可以分为前导模糊查询、后导模糊查询和全导匹配查询，适用于查询某个字符串中是否包含另一个模糊查询的场景。 select * from user where hobby like "%yanggb%"; 使用的场景局限于找到hobby中存在yanggb的记录（hobby为多个以逗号隔开的值），反之不行。 方法2：使用M
sql server分组排序并取出每组中的第一条数据
Saropetry的博客
06-02 5976
使用SQL Server数据库在【分组排序并取出每组中的第一条数据】的场景下，很容易想到的是使用GROUP BY分组子句配合聚合函数。 举个简单的例子，有一个YANGGBS表，表中有NAME和AGE两个字段，要求统计出每个NAME的最大AGE。 SELECT AA.* FROM YANGGBS AA INNER JOIN ( SELECT NAME, MAX(AGE) FROM YANGGBS GROUP BY NAME ) BB .
tableau怎么连接hadoop
Saropetry的博客
01-11 1462
tableau连接服务器Cloudera Hadoop，直接打开tableau连接肯定不行啦 工具/原料 Tableau2018.3.1（64位） VMware Workstation Ubuntu hadoop2.5.0 hive0.13.1 方法/步骤 1 cd到你的hadoop安装目录下，分别运行以下命令： sbin/ha...
写SQL语句的小技巧
Saropetry的博客
06-05 1223
一、SQL总结写法 　　SQL的写法无非就是几种,关联查询,子查询,分组函数,各种函数的使用 　　1.首先根据要做的需求,先分析一下,需要用到哪些查询,例如要用到关联查询,就先把用到的表列出来,比如a,b,c三个表就先写出来,select * from a,b,c 　　前面的内容可以先放着不用谢,然后找三张表的之间的关联关系,这里要看的连接方式是自然连接,还是左连接,右连接等 　　2.举例: 　　select * from a,b,c where a.id = b.aid and b.id =..
group by 分组查询、limit分页查询
Saropetry的博客
06-07 1167
group by 分组查询 1、单独使用（毫无意义），类似于去重 2、与group_concat()函数一起使用 按固定字段，把字符用‘，’串联起来 select gradeName,group_concat(stuName) from t_student group bygradeName 3、与聚合函数一起使用 4、与having一起使用（限制输出的结果，即将查询结果筛选条件过滤） 5、与with rollup一起使用（最后加入一个总和行） （1）对于int 类型的聚合...
numpy 数组shape()
Saropetry的博客
07-27 1076
import numpy as np # t1 一维数组 t1 = np.arange(12) print(t1,t1.shape) # 查看数组的形状 print(t1.shape) # t1 二维数组 t2 = np.array([[1,2,3],[4,5,6]]) print(t2,t2.shape) # t3 三维数组 t3 = np.array([[[1,2,3],[4,5,6]],[[7,8,9],[10,11,12]]]) print(t3,t3.shape) # 结果 [ 0 .
Mysql、Hive语法对比
Saropetry的博客
06-09 1074
Mysql ——————学生表——---- CREATE table student( student_id varchar(4) ---学生编号 ,name varchar(20) ---学生姓名 ,birthday varchar(10) ---学生生日 ,sex varchar(2) ---学生性别 ); CREATE TABLE `student` ( `student_id` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT...
R语言安装使用
Saropetry的博客
08-14 390
1、R包的安装与使用：http://www.360doc.com/content/18/1012/08/53349168_794029077.shtml 2、怎样找到合适的R包：http://www.360doc.com/content/18/1012/08/53349168_794029175.shtml 举例：查找广义线性模型GAM > library('sos') > findFn('gam') 3、R语言数据的导入、导出 http://www.360doc.com/con
Mysql中时间、字符串、时间戳之间转换关系
Saropetry的博客
06-03 343
1、时间→字符串 date_format(日期， 格式字符串str) ,str为转换后的指定字符串格式： select date_format(NOW(),'%Y-%m-%d %H:%i:%s') select date_format(NOW(),'%Y%m%d %H:%i:%s') 2、字符串→时间 mysql的数据进行分析时，经常会遇到将业务意义为时间类型的变量定义为字符串的情况。如果我们需要按照时间维度进行分析的话，首先先要将字符串转换为时间类型。 str_to_date(字符串str..
MySQL练习
Saropetry的博客
06-05 241
昨天闲逛的时候，碰到这么一个地方：免费的编程中文书籍索引由于没有联系到作者，于是只在这里放上一个链接，该贡献里面涉及到了很多优秀的学习编程的资源，也是一样希望可以帮助到大家。 既然要学习数据库，就一定要明白什么事数据库（以下来自百度百科解释） ，通俗的来说，数据库就是用来存储信息的。 数据库，简单来说是本身可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。 数据库指的是以一定方式储存在一起、能为多个用户共享、具有尽可能小的冗余度的特点、是与应...
数据质量问题处理SOP流程
Saropetry的博客
09-07 217
数据质量问题排查与开发上线流程相反，采用“反向排查”方法。
35-36Python 学习笔记 —— OS模块常用接口
Saropetry的博客
07-19 188
文件系统模块：OS 目录 的属性和方法 getcmd() : 获取当前工作目录 chdir() : 改变当前工作目录 chroot() : 设定当前进程的根目录 listdir() : 列出指定目录下的所有文件名 mkdir() : 创建指定目录 makedirs() : 创建多级目录 rmdir() : 移除目录 removedirs() : 删除多级目录 文件 的属性和方法 mkfifo() : 创建命名管道，先进先出管道 mknod() : 创建设备文件 remove() :
机器学习、深度学习资料汇总
Saropetry的博客
09-07 170
深度学习入门Fast.ai 2.0上线！自带中文字幕，所有笔记、资源全部免费！：https://mp.weixin.qq.com/s/W2iau9VEEocl20XlBP1Keg 课程介绍 https://www.fast.ai/2020/08/21/fastai2-launch/ 教学视频 https://course19.fast.ai/index.html 亚马逊链接 https://www.amazon.com/Deep-Learning-Coders-fastai-PyTorch/dp/1
上期api demo
最新发布
11-07
接下来，我们向参与者演示了几个不同的API功能。例如，我们展示了如何使用地理位置API来获取特定位置的经纬度信息，并如何使用天气API来获取该位置的实时天气数据。我们还介绍了如何使用支付API进行在线支付交易，并...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值