MySQL 基本查询21--26

题目21：查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率（及格：>=60），中等率（中等为：70-80），优良率（优良为：80-90），优秀率（优秀为：>=90）；—比较综合，多看！

一道比较综合的题目

SELECT
	t1.c_id,
	t2.c_name
    ,MAX(t1.s_score) max_score
	,MIN(t1.s_score) min_score
	,ROUND(AVG(t1.s_score),2) avg_score
	,ROUND(100*(SUM(CASE WHEN t1.s_score>=60 THEN 1 ELSE 0 END)/sum(CASE WHEN t1.s_score THEN 1 ELSE 0 END)),2) AS '及格率'
	,ROUND(100*(SUM(CASE WHEN t1.s_score>=70 AND t1.s_score<=80 THEN 1 ELSE 0 END)/sum(CASE WHEN t1.s_score THEN 1 ELSE 0 END)),2) AS '中等率'
	,ROUND(100*(SUM(CASE WHEN t1.s_score>=80 AND t1.s_score<=90 THEN 1 ELSE 0 END)/sum(CASE WHEN t1.s_score THEN 1 ELSE 0 END)),2) AS '优良率'
	,ROUND(100*(SUM(CASE WHEN t1.s_score>=90 THEN 1 ELSE 0 END)/sum(CASE WHEN t1.s_score THEN 1 ELSE 0 END)),2) AS '优秀率'
FROM
	score t1
JOIN course t2 ON t1.c_id = t2.c_id
GROUP BY
	t1.c_id,
	t2.c_name;

题目22：按照各科成绩进行排序，并且显示排名—比较综合，多看

MySQL进行排序、排名方式

select * from (select 
                t1.c_id,
                t1.s_score,
                (select count(distinct t2.s_score) 
                 from Score t2 
                 where t2.s_score>=t1.s_score and t2.c_id='01') rank
              from Score t1 where t1.c_id='01'
              order by t1.s_score desc) t1

union
select * from (select 
                 t1.c_id
                 ,t1.s_score
                 ,(select count(distinct t2.s_score)
                   from Score t2 
                   where t2.s_score>=t1.s_score and t2.c_id='02') rank
               from Score t1 where t1.c_id='02'
               order by t1.s_score desc) t2

union
  select * from (select 
                  t1.c_id,
                  t1.s_score,
                  (select count(distinct t2.s_score) from Score t2 where t2.s_score>=t1.s_score and t2.c_id='03') rank
                from Score t1 where t1.c_id='03'
                order by t1.s_score desc) t3

题目23：查询学生的总成绩，并进行排名—比较综合，多看！

select 
 t1.s_id ,t1.s_name, t1.score
 ,(select count(t2.score)
    from(select s.s_id, s.s_name, sum(sc.s_score) score
         from Student s
         join Score sc
         on s.s_id = sc.s_id
         group by s.s_id
         order by 3 desc)t2   -- t2和t1相同
    where t2.score >= t1.score) as rank
from(
  select s.s_id ,s.s_name ,sum(sc.s_score) score
  from Student s
  join Score sc
  on s.s_id = sc.s_id
  group by s.s_id
  order by 3 desc)t1   -- t1
order by 3 desc;

题目24：LeetCode-SQL-182-查找重复的电子邮箱，从给定的表Person中找出重复的电子邮箱

-- 方法1
select 
    Email
from Person 
group by Email
having count(Email) > 1;  -- 过滤条件

-- 方法2
select 
    distinct (p1.Email)  -- 去重统计邮箱
from Person p1
join Person p2 on p1.Email = p2.Email and p1.Id != p2.Id;  -- 指定连接条件

题目25：LeetCode-SQL-595-大的国家

select 
    name
    ,population
    ,area
from World 
where area > 3000000
or population > 25000000; 

题目26：LeetCode-SQL-184-部门工资最高/N高的员工—多看

-- 方法1
SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM Employee 
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (Employee.DepartmentId , Salary) IN (  -- 两个字段同时使用
  SELECT 
   DepartmentId, -- 部门分组找出部门号和薪水的最大值
   MAX(Salary)  
  FROM Employee 
  GROUP BY DepartmentId
)

-- 方法2：
select 
    d.Name  Department
    ,e.Name  Employee
    ,e.Salary  Salary
from Employee e , Department d 
where e.DepartmentId = d.Id  --  在同一个部门中进行比计较
and e.Salary >= (select max(Salary) from Employee where DepartmentId=d.Id);  -- 找出每个部门的最高值；如果大于等于这个最高值，肯定是最高的