sql语句练习

1661. 每台机器的进程平均运行时间 - 力扣（LeetCode）

select machine_id,round(sum(if(activity_type = 'end',timestamp,-timestamp))/count(distinct process_id),3) 
as processing_time from Activity group by machine_id 

if(activity_type = 'end',timestamp,-timestamp),if条件成立则sum+timestamp,否则

sum+(-timestamp)

count(distinct process_id),统计不同id的个数

round(x,3),让x保留三位小数

577. 员工奖金 - 力扣（LeetCode）

select name,bonus from Employee f left join Bonus b on f.empId=b.empId 
where b.bonus<1000 or b.bonus is null

a left join b on a.id=b.id 会显示出对于a表各个列的结果，而加上过滤条件后对于a没有的列则会给出null值

1280. 学生们参加各科测试的次数 - 力扣（LeetCode）

select a.student_id,a.student_name,b.subject_name,count(e.subject_name) as attended_exams from
(Students a,Subjects b) left join Examinations e on a.student_id=e.student_id and e.subject_name=b.subject_name
group by  student_id, student_name,subject_name
order by student_id,student_name

三个表进行连接，使用left join以

Examinations表为基准来显示，用科目名字和学生id把三个表连接起来，根据样例结果来看，我们把学生id,学生名字，科目名字进行聚合分组，再根据id大小和名字字典序进行排列

620. 有趣的电影 - 力扣（LeetCode）

select *  from cinema where description <> 'boring' and id&1 order by rating desc

id&1表示id必须为计数,<>等同于!=