sql基础day05

1、1280. 学生们参加各科测试的次数

#思路：通过观察结果表，发现每个学生的每个科目都展示了出来，所以将学生表和科目表进行笛卡尔
#再通过子查询查找成绩表中学生id和科目名称相同的数据进行计数
select stu.student_id, stu.student_name, sub.subject_name, count(e.student_id) as attended_exams
from Subjects sub join Students  stu left join Examinations e
on e.subject_name = sub.subject_name and e.student_id = stu.student_id
group by sub.subject_name, stu.student_id
order by stu.student_id, sub.subject_name;

2、1501. 可以放心投资的国家

• 先找到所有国家的平均时长
• 再去找各个国家的平均时长
• 关键点：用union all 将每个人的通话时长都能进行统计，再去关联person表和country表，对country进行分组并计算出各个国家的平均时长，最后筛选出大于所有国家的平均时长的国家

select t3.country
from (
         select cname country, sum(duration) / count(1) duration
         from (select caller_id, duration
               from calls
               union all
               select callee_id, duration
               from calls) t1
                  left join (
             select p.id, p.phone_number, p.name pname, c.name cname, country_code
             from person p
                      join country c on substring(p.phone_number, 1, 3) = c.country_code
         ) t2 on t1.caller_id = t2.id
         group by t2.cname, country_code
     ) t3
where t3.duration > (select avg(duration)
                     from (select duration
                           from calls
                           union all
                           select duration
                           from calls) t);

以上代码可优化：关键点：求平均值根据原表也可以求出来

SELECT c1.name country
FROM Person p LEFT JOIN Country c1
  ON LEFT(p.phone_number, 3) = c1.country_code
  JOIN (
      SELECT caller_id id, duration FROM Calls
      UNION
      SELECT callee_id id, duration FROM Calls
  ) c2 ON c2.id = p.id
GROUP BY c1.name
HAVING AVG(duration) > (SELECT AVG(duration) FROM Calls)

3、184. 部门工资最高的员工

select salary, d.name, e.name, d.id
from employee e,
     department d
where e.departmentId = d.id
and e.salary in (select max(salary) from employee group by departmentId)

4、580. 统计各专业学生人数

select dept_name,count(student_id) student_number
from department left join student s on department.dept_id = s.dept_id
group by dept_name
order by student_number desc,dept_name ;

5、1294. 不同国家的天气类型

#慎用having
select
       distinct country_name,if(avg(weather_state)<=15,'Cold',if(avg(weather_state)>=25,'Hot','Warm')) weather_type
from weather left join countries c on weather.country_id = c.country_id
where day like '2019-11%'
group by country_name

6、626、换座位

• 这道题操作id更加简单

select case
           when mod(id, 2) = 1 and id = (select count(*) from seat) then id
           when mod(id, 2) = 1 then id + 1
           else id - 1
           end as id,
       student
from seat
order by id