MySQL Super Classic 50 вопросов — вопросы с 21 по 25

MySQL50-7 - вопросы 21-25

В этой статье представлены темы 21–25. Основные вопросы, связанные с этим:

• Сумма групповой статистики, процент
• Как использовать SQL для сортировки
• использование
• союз сращивания

5 тем:

• запрос отличаетсяучительучили по-другомукурс среднийОтображение от высокого к низкому
• Запросить информацию о студентах, занявших со 2-го по 3-е место во всех курсах и оценках курса.
• Подсчитайте количество людей по каждому предмету и классу: номер курса, название курса, [100-85], [85-70], [70-60], [0-60] и процент
• Запрос средней оценки и рейтинга учеников
• Запросить записи трех лучших результатов по каждому предмету

тема 21

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

запрос отличаетсяучительучили по-другомукурс среднийОтображение от высокого к низкому

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

Используемые таблицы в основном

Учитель: Учитель

Курс: Курс, как основная таблица

Оценка: Оценка

Его можно получить, соединив 3 стола

Реализация SQL

Начните с выяснения того, какие курсы преподает каждый учитель:

select 
	c.c_name
	,t.t_name
from Course c
left join Teacher t
on c.t_id = t.t_id;

Комбинируя вышеуказанные результаты слист оценоксоедините их:

select 
	c.c_name
	,t.t_name
	,round(avg(s.s_score),2)  score   -- 课程分组后再求均值
from Course c   -- 主表，通过两次连接
left join Teacher t
on c.t_id = t.t_id
left join Score s
on c.c_id = s.c_id
group by c.c_id   -- 课程分组
order by 3 desc;  -- 降序

тема 22

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

Запросить информацию о студентах, занявших со 2-го по 3-е место во всех курсах и оценках курса.

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

Оценка: Оценка

Информация о студенте: Студент

Мы соединили 2-й и 3-й по каждому предмету, а затем вынули информацию о студенте.

Реализация SQL

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

1. Расписание курса и лист оценок подключены для отображения всей информации о курсе и оценках.

select 
	s.s_id
	,s.c_id
	,s.s_score
	,c.c_name
from Score s
join Course c
on s.c_id = c.c_id

2. Узнайте все баллы по языку

select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc;

3. Находим 2-го и 3-го учеников китайского

select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc
limit 1, 2;

4. Получите баллы по китайскому языку, математике и английскому языку одновременно и сдайтеunionсоединение

-- union连接

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

union

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

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

5. Подключите приведенную выше таблицу результатов с информацией о студентах.

По крайней мере, это сбылось ?

-- 最终脚本
-- ！！！！真的需要好好优化下

select
	s.s_id
	,s.s_name
	,t.c_name
	,t.s_score
from Student s
join (-- union连接

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

union

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

union
((select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '英语'
order by s.s_score desc
limit 1, 2)))t  -- 临时表t

on s.s_id = t.s_id

Тот же метод, что и вопрос 25

1. Взяв в качестве примера китайский язык, сначала находим 3 верхние оценки (включая одинаковые оценки)

-- 语文
select
	a.s_id
	,a.c_id
	,a.s_score   -- 3、此时a表的成绩就是我们找的  
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score  -- 1、判断a的分数小于等于b的分数，要带上等号
and a.c_id="01"
group by 1,2
having count(b.s_id) <= 3  -- 2、b中的个数至少有3个，应对分数相同的情形
order by 3 desc
limit 1,2

-- 语文
select
	a.s_id
	,a.c_id
	,a.s_score   -- 3、此时a表的成绩就是我们找的  
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score  -- 1、判断a的分数小于等于b的分数，要带上等号
and a.c_id="01"
group by 1,2
having count(b.s_id) <= 3  -- 2、b中的个数至少有3个，应对分数相同的情形
order by 3 desc
limit 1,2;   -- 取得第2、3名

Получите 2-й и 3-й классы через аналогичные операции по математике и английскому языку, а потом можно делать сплайсинг.

тема 23

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

Подсчитайте количество людей по каждому предмету и классу: номер курса, название курса, [100-85], [85-70], [70-60], [0-60] и процент

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

Курс: Курс

Оценка: Оценка

Оператор case используется для суждения, оператор count используется для статистики, а оператор sum используется для суммирования.

Реализация SQL

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

1. Как отображать каждую оценку в группах: ABCD представляет соответствующую оценку

select 
	c_id
	,s_score
	,case when s_score >= 85 and  s_score<= 100 then 'A'   -- 大小关系必须分两次写，一次写的话MySQL无法识别
		when 70 <= s_score and s_score < 85 then 'B'
		when 60 <= s_score and s_score < 70 then 'C'
		when 0 <= s_score and s_score < 60 then 'D'
		else '其他' end as 'category'
from Score s;

2. Свяжите две таблицы для отображения данных

-- 1、查看全部课程和成绩信息

select 
	s.c_id
	,c.c_name
	,s.s_score
	,case when s.s_score >= 85 and  s.s_score<= 100 then 'A'   -- 大小关系必须分两次写，一次写的话MySQL无法识别
		when 70 <= s.s_score and s.s_score < 85 then 'B'
		when 60 <= s.s_score and s.s_score < 70 then 'C'
		when 0 <= s.s_score and s.s_score < 60 then 'D'
		else '其他' end as 'category'
from Score s
join Course c 
on s.c_id = c.c_id;

3. Полный код

select
	s.c_id  编号
	,c.c_name  科目
	,sum(case when s.s_score >= 85 and  s.s_score<= 100 then 1 else 0 end) "[85,100]人数"
	,round(100 * (sum(case when s.s_score >= 85 and  s.s_score<= 100 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[85,100]百分比'
	,sum(case when s.s_score >= 70 and  s.s_score<= 85 then 1 else 0 end) "[70,85]人数"
	,round(100 * (sum(case when s.s_score >= 70 and  s.s_score<= 85 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[70,85]百分比'
	,sum(case when s.s_score >= 60 and  s.s_score<= 70 then 1 else 0 end) "[60,70]人数"
	,round(100 * (sum(case when s.s_score >= 60 and  s.s_score<= 70 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[60,70]百分比'
	,sum(case when s.s_score >= 0 and  s.s_score<= 60 then 1 else 0 end) "[0,60]人数"
	,round(100 * (sum(case when s.s_score >= 0 and  s.s_score<= 60 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[0,60]百分比'
from Score s
left join Course c
on s.c_id = c.c_id
group by s.c_id, c.c_name

Эталонный метод

1. Сначала подсчитайте количество и долю каждого этапа

select 
	c_id
	,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100'
	,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '占比'
from Score 
group by c_id;  -- 分课程统计总数和占比


-- 方式2
select 
	c_id
	,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100'
	,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(case when s_score then 1 else 0 end)), 2) '占比'   -- 不同count(*)
from Score 
group by c_id;  

Обратите внимание на сравнение:

2. Выясним 4 случая одновременно

select 
	c_id
	,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100'
	,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '[85,100]占比'
	,sum(case when s_score > 70 and s_score <=85 then 1 else 0 end) as '70-85'
	,round(100 * (sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) / count(*)), 2) '[70,85]占比'
	,sum(case when s_score > 60 and s_score <=70 then 1 else 0 end) as '60-70'
	,round(100 * (sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) / count(*)), 2) '[60,70]占比'
	,sum(case when s_score >=0 and s_score <=60 then 1 else 0 end) as '0-60'
	,round(100 * (sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) / count(*)), 2) '[0,60]占比'
from Score 
group by c_id;  -- 分课程统计总数和占比

3. Соедините имена предметов

-- 整体和自己的方法是类似的
select 
	s.c_id
	,c.c_name
	,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100'
	,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '[85,100]占比'
	,sum(case when s_score > 70 and s_score <=85 then 1 else 0 end) as '70-85'
	,round(100 * (sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) / count(*)), 2) '[70,85]占比'
	,sum(case when s_score > 60 and s_score <=70 then 1 else 0 end) as '60-70'
	,round(100 * (sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) / count(*)), 2) '[60,70]占比'
	,sum(case when s_score >=0 and s_score <=60 then 1 else 0 end) as '0-60'
	,round(100 * (sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) / count(*)), 2) '[0,60]占比'
from Score s
left join Course c
on s.c_id = c.c_id
group by s.c_id, c.c_name;    -- 分课程统计总数和占比

тема 24

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

Запрос средней оценки и рейтинга учеников

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

Студент: Студент

Оценка: Оценка

Среднее: функция среднего

Ранжирование: Решено путем сортировки

Реализация SQL

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

1. Сначала найдите средний балл каждого человека

-- 自己的方法

select 
	sc.s_id
	,s.s_name
	,round(avg(sc.s_score),2)  avg_score
from Score sc
join Student s
on sc.s_id=s.s_id
group by sc.s_id,s.s_name

2. Сортируем результаты выше

! ! !В MySQL5 нет функции ранжирования, вам нужно реализовать функцию сортировки самостоятельно

-- MYSQL5.7中没有rank函数，所以通过自连接实现

select
	t1.s_id
	,t1.s_name
	,t1.avg_score
	,(select count(distinct t2.avg_score) 
		from (select 
            sc.s_id
            ,s.s_name
            ,round(avg(sc.s_score),2)  avg_score
          from Score sc
          join Student s
          on sc.s_id=s.s_id
          group by sc.s_id,s.s_name)t2    -- 临时表t2也是上面的结果
		where t2.avg_score >= t1.avg_score
		) rank

from (select 
        sc.s_id
        ,s.s_name
        ,round(avg(sc.s_score),2)  avg_score
      from Score sc
      join Student s
      on sc.s_id=s.s_id
      group by sc.s_id,s.s_name)t1   -- 临时表t1就是上面的结果
order by t1.avg_score desc;

Эталонный метод

select
	a.s_id  -- 学号
	,@i:=@i+1 as '不保留空缺排名'   -- 直接i的自加
	,@k:=(case when @avg_score=a.avg_s then @k else @i end) as '保留空缺排名'
	,@avg_score:=avg_s as '平均分'  -- 表a中的值

from (select 
      	s_id
      	,round(avg(s_score), 2) as avg_s
      from Score 
      group by s_id
      order by 2 desc)a    -- 表a：平均成绩的排序和学号
      ,(select @avg_score:=0, @i:=0, @k:=0)b   -- 表b：通过变量设置初始值

Реализуйте функцию ранга

select 
	s.s_name  -- 姓名
	,s.s_score  -- 成绩
	,(select count(distinct t2.s_score)
    from Score t2
    where t2.s_score >= t1.s_score) rank   -- 在t2分数大的情况下，统计t2的去重个数
from Score t1
order by t1.s_score desc;   -- 分数降序排列

Пример для иллюстрации этого скрипта:

Имя	достижение
Чжан Сан	89
Ли Си	90
Ван Ву	78
Сяо Мин	98
немного красный	60

1. Когда t1.s_score=89, t2.s_score >= t1.s_score98, 90 и 89, в это время количество count(различных t2.s_score) равно 3
2. Когда t1.s_score=90, t2.s_score >= t1.s_score имеет98 и 90, в это время количество count(различных t2.s_score) равно 2
3. Когда t1.s_score=78, t2.s_score >= t1.s_score имеет98, 90, 89 и 78, в это время количество count(различных t2.s_score) равно 4
4. Когда t1.s_score=98, только те, которые удовлетворяют t2.s_score >= t1.s_score98, в это время количество count(различных t2.s_score) равно 1
5. Когда t1.s_score=60, t2.s_score >= t1.s_scoreЕсть 89, 90, 78, 98, 60, в это время количество count(различных t2.s_score) равно 5

Выполнив описанные выше шаги, мы обнаружили, что:Число, соответствующее каждой оценке в t1, является ее рангом.

тема 25

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

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

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

Этот вопрос и вопрос 22 относятся к одному и тому же типу: чтобы найти оценки указанных рейтингов по каждому предмету, используется следующая таблица:Score

Реализация SQL

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

1. Сначала мы находим топ-3 языка

select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc   -- 降序之后取出前3条记录
limit 3;

2. Таким же методом можем найти первые 3 записи по математике и английскому языку, а потом соединить их объединением, которое нужно оптимизировать ?

-- 自己的脚本

(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc   -- 降序之后取出前3条记录
limit 3)

union

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

union

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

Эталонный метод

Через самоподключение таблицы Score значение в таблице а меньше значения в таблице б, после сортировки берем первые 3

select
	a.s_id
	,a.c_id
	,a.s_score   -- 取出a中的成绩
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score   -- 表b中的成绩大
group by 1,2,3
having count(b.s_id) = 3
order by 2, 3 desc;

мы проходим语文Этот предмет, чтобы понять приведенный выше код:Топ-3: 80, 80, 76

-- 语文
select
	a.s_id
	,a.c_id
	,a.s_score   -- 3、此时a表的成绩就是我们找的  
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score  -- 1、判断a的分数小于等于b的分数，要带上等号
and a.c_id="01"
group by 1,2
having count(b.s_id) <= 3  -- 2、b中的个数至少有3个，应对分数相同的情形
order by 3 desc;   

-- 语文

select
	a.s_id
	,a.c_id
	,a.s_score   -- a表的成绩
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score   -- 1、判断a的分数小于等于b的分数，要带上等号
group by 1,2,3
having count(b.s_id) <= 3   -- 2、b中的个数至少有3个，应对分数相同的情形
order by 2, 3 desc;   -- 课程（2）的升序，成绩（）3的降序