力扣sql基础50记录

9.上升的温度

想要求每一个比昨天温度高的日子的id号。

思路：使用 dateadd函数来完成昨日的判定，加上温度比较完成

核心：dateadd(时间颗粒度,num,比较日期) 

select b.id as Id
from weather a,weather b
where b.recorddate=dateadd(day,1,a.recorddate) and a.temperature < b.temperature;

10.每台机器的进程平均运行时间

一台机器存在同样多个进程，每个进程存在'start'和'end'两个时间戳，需要求出每台机器完成所有进程消耗的平均时间。

思路：可以直接将计算的值作为一个值返回，只需要完成公式的计算   1.通过分表链接，将一个表通过链接将end和start放在一个元组中再进行计算，这个时候可以只写使用avg加上group by机器id，然后用round函数四舍五入保留三位小数。

核心：round(数,保留位数)，思路

select a.machine_id,
round(avg(a.timestamp-b.timestamp),3) as processing_time
from activity a join activity b on a.machine_id=b.machine_id and a.process_id=b.process_id and a.activity_type='end'
and b.activity_type='start'
group by a.machine_id;

11.员工奖金

左连接加判定是否为null，水题

select name,bonus
from employee left join bonus on employee.empid=bonus.empid
where bonus <1000 or bonus is null;

12.学生们参加各科测试的次数

有三个表，一个表存学生id和他的名字，一个存所有的科目，剩下一个存考试记录，有学生id和对应的考试科目（如果有对应表示参加了一次该科目考试）。现在目标求一个表，把学生id和学生名字和考试科目和考试次数统计。（就算没有参加该考试也需要将考试次数统计成0）

思路：首先需要求一个学生和考试科目的笛卡尔积，然后将这个笛卡尔积的表和我们的exam表进行一个左外连接，再对左外链接的表进行一个count(*），group by student_id，subject_name（表示同时满足两个相等的情况下分组）。最后统计的时候需要对null赋值一个0.

核心：cross join(笛卡尔积）,思路，group by的含义，isnull(value1，value2）如果value1不为null返回value1否则返回value2

select students.student_id,students.student_name,subjects.subject_name,isnull(count(examinations.subject_name),0) as attended_exams
from students
cross join subjects 
left join examinations on examinations.student_id=students.student_id and examinations.subject_name=
subjects.subject_name
group by subjects.subject_name , students.student_id,students.student_name
order by students.student_id,subjects.subject_name

13.至少有五名直接下属的经理

给出一个表，有员工id，和他的名字以及所属的上司，需要求有五名直接下属的员工的名字和id

思路，通过该表内连接自己将经理名字接在后面，然后group by上司id和名字，最后用一个count将所有>=5的输出出来（用子查询好像比较好）

核心：group by就算知道它是一样也要写出来，不然会报错

select name
from (select count(a.managerid) as cnt_manager,b.name,a.managerid
from employee as a join employee as b on a.managerid = b.id
group by a.managerid,b.name) as cnt_manager(cnt_manager,name,managerid)
where cnt_manager>=5;

14.确认率

思路：需要求一个当左连接为null情况下统计数字为0的情况，这种情况我只知道使用isnull来将null转化成0来输出，但是实际上有sum(iif(action='confirmed',1,0)的iff的巧妙办法来完成我需要count和isnull联合转化的需求的操作。四舍五入可以使用round或者是convert都可以。

核心：sum的iif语句转化和对于左连接null的思考。

select a.user_id,round(sum(iif(b.action='confirmed',1,0))*1.0/count(a.user_id),2) as confirmation_rate
from signups a left join confirmations b on a.user_id =b.user_id 
group by a.user_id;

15.有趣的电影

大水题

select id,movie,description,rating
from cinema
where description<>'boring' and id%2<>0
order by rating desc;

16.平均售价

最后一个点卡null，直接写的化会给出一个null，需要在被除数和除数特判一个null，或者直接答案特判null也可以。

select b.product_id,isnull(round(sum(a.units*b.price*1.0)/sum(a.units),2),0) as average_price
from prices as b left join unitssold as a on a.product_id = b.product_id and a.purchase_date between b.start_date and b.end_date
group by b.product_id;

17.项目员工1

内连接加上一个sum的运用和count的运用

select project_id,round(sum(b.experience_years)*1.0/count(a.employee_id),2) as average_years
from project as a join employee as b on b.employee_id=a.employee_id
group by a.project_id;

18.各赛事的用户注册率

思路：题目上感觉非常像12题，但是这一题求的是相对于比赛的参加人数占所有人数的的百分比，可以直接使用内连接，不像12题需要把所有学生参加所有科目的笛卡尔积和真实报名科目进行左连接。

核心：思路比较难，嵌套的round里的子查询是一个新的知识点。

select b.contest_id,round(count(b.user_id)*1.0/(select count(*) from users)*100,2) as percentage
from users as a join register as b on a.user_id = b.user_id 
group by b.contest_id
order by percentage desc,contest_id