MySQL50 вопросов _ вопросы с 41 по 45

база данных
MySQL50 вопросов _ вопросы с 41 по 45

MySQL50-11-Вопросы 41-45

В этой статье представлены вопросы 41-45, которые в основном включают очки знаний:

  • Сведения о сравнении запросов на самосоединение для таблиц
  • Найдите 2 лучших
  • Групповая сортировка + наличие фильтрации

5 тем

  • Запрос идентификатора учащегося, идентификатора курса и оценок учащихся с одинаковыми оценками на разных курсах.
  • Лучшие топ-2 по каждому предмету
  • Подсчитайте количество студентов, изучающих факультативы по каждому курсу (только для курсов с более чем 5 студентами). Требуется вывести номер курса и количество факультативов.Результаты запроса располагаются в порядке убывания количества человек, если количество людей одинаковое, то в порядке возрастания номера курса.
  • Получить идентификационные номера студентов для студентов, изучающих как минимум два курса
  • Запросить информацию о студентах, прошедших все курсы

Тема 41

требования темы

Запрос идентификатора учащегося, идентификатора курса и оценок учащихся с одинаковыми оценками на разных курсах.

Процесс анализа

  • Оценки за курс: Score, s_score
  • Идентификатор учащегося: оценка, s_id
  • Идентификатор курса: Score, c_id

3 поля находятся в одной таблице одновременно, поэтому мы можем передатьСамостоятельное соединение таблицы Scoreобнаружить

Реализация SQL

select
	a.s_id
	,a.c_id
	,a.s_score
from Score a
join Score b
on a.c_id != b.c_id
and a.s_score = b.s_score
and a.s_id != b.s_id;

image-20201122001411784

Нам также нужно переделать номер студента:

select
	distinct a.s_id
	,a.c_id
	,a.s_score
from Score a
join Score b
on a.c_id != b.c_id
and a.s_score = b.s_score
and a.s_id != b.s_id;

Посмотрим, соответствуют ли исходные данные требованиям:

тема 42

требования темы

Запросите два лучших с лучшими результатами по каждому предмету

Процесс анализа

Требование вопроса состоит в том, чтобы найти двух лучших учеников в каждом классе.

  • Оценка: Оценка
  • Тема: Курс

Реализация SQL

собственный метод

Еще нужно оптимизировать ?

-- 先找出语文的前2名同学

select 
	c.c_id
	,sc.s_id
	,sc.s_score	
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '语文'   -- 改成数学和英语即可求出相应的信息
order by sc.s_score desc
limit 2;

Ответ можно найти, объединив информацию из трех дисциплин:

-- 最终脚本

(select 
	c.c_id
	,sc.s_id
	,sc.s_score	
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '语文'   
order by sc.s_score desc
limit 2)

union

(select 
	c.c_id
	,sc.s_id
	,sc.s_score	
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '数学'   
order by sc.s_score desc
limit 2)

union

(select 
	c.c_id
	,sc.s_id
	,sc.s_score	
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '英语' 
order by sc.s_score desc
limit 2)

Эталонный метод (хороший метод)

Как решить проблему с топами ??? Слишком классно

select 
	a.c_id
	,a.s_id
	,a.s_score
from Score a
where (select count(1)   -- count(1)类似count(*)：统计表b中分数大的数量
       from Score b 
       where b.c_id=a.c_id   -- 课程相同
       and b.s_score >= a.s_score) <= 2   -- 前2名
order by a.c_id;

Во-первых, давайте посмотрим на реальные данные.Давайте используем курс 01, чтобы объяснить приведенный выше код:

удовлетворять потребностиcount(1)<=2есть только два случая

Еще нужно понять ?

тема 43

требования темы

Подсчитайте количество студентов, изучающих факультативы по каждому курсу (только для курсов с более чем 5 студентами). Требуется вывести номер курса и количество факультативов.Результаты запроса располагаются в порядке убывания количества человек, если количество людей одинаковое, то в порядке возрастания номера курса.

Процесс анализа

Идентификатор курса: Score, c_id

Ученик: Оценка, s_id

Реализация SQL

select 
	c_id
	,count(s_score) num
from Score 
group by c_id
having num > 5
order by num desc, c_id;

тема 44

требования темы

Получить идентификационные номера студентов для студентов, изучающих как минимум два курса

Процесс анализа

Курс: Оценка, c_id

Идентификатор учащегося: оценка, s_id

Реализация SQL

Результаты показывают, что все требования соблюдены.

select  
	s_id
	,count(*) num
from Score 
group by s_id
having num >= 2;

тема 45

требования темы

Запросить информацию о студентах, прошедших все курсы

Процесс анализа

  1. Во-первых, давайте посмотрим на таблицу курсов, чтобы узнать, каково общее количество курсов (число)
  2. Затем узнайте информацию о студенте, курс которого указан в таблице баллов.

Реализация SQL

собственный метод

1. Общее количество курсов num

select count(*) from Course;   -- 总共3门

image-20201122095253954

2. Подсчитайте количество курсов для каждого человека в группах из таблицы Score и ознакомьтесь с информацией о студенте 3.

select 
	s_id
	,count(c_id) num   -- 课程数目
from Score
group by s_id
having num in (select count(*)  
               from Course); --满足全部课程

3. Мы можем найти информацию о студенте в приведенных выше результатах.

select 
	s.*
	,count(c_id) num   -- 课程数目
from Score sc
join Student s
on sc.s_id = s.s_id
group by s.s_id
having num in (select count(*)  
               from Course); --满足全部课程

Эталонный метод
select *   -- 3、s_id对应的学生信息
from Student 
where s_id in(select s_id -- 2、最大课程数对应的s_id
              from Score 
              group by s_id 
              having count(*)=(select count(*) from Course)  -- 1、全部课程数
             )
Категории