2356,2372,2377,2388,2394

重点2388题

---

2356. Number of Unique Subjects Taught by Each Teacher

# Write your MySQL query statement below
select teacher_id,count(distinct subject_id) cnt
from Teacher 
group by teacher_id

2372. 计算每个销售人员的影响力

# Write your MySQL query statement below
select S.salesperson_id , S.name ,ifnull(sum(price),0) total
from Salesperson S left join Customer C 
on C.salesperson_id = S.salesperson_id
LEFT JOIN Sales sa
on C.customer_id = sa.customer_id
group by S.salesperson_id

---

2377. Sort the Olympic Table

# Write your MySQL query statement below
select *
from Olympic
order by 2 desc,3 desc,4 desc,1

2388. Change Null Values in a Table to the Previous Value(用户变量)

# Write your MySQL query statement below
select id,
case
when drink is not null then @myself :=drink
else @myself
end drink
from CoffeeShop

2394. Employees With Deductions

# Write your MySQL query statement below
select E.employee_id
from(
select employee_id,
sum(ceil(timestampdiff(second,in_time,out_time)/60)/60)  total
from Logs
group by employee_id)new_table right join Employees E 
on E.employee_id = new_table.employee_id
where new_table.total < needed_hours or new_table.total is null

---

总结

ceil 向上取整 floor向下取整

round四舍五入、truncate四舍五不入

select '正常除法' as '含义',10/6
union select '四舍五入 round' ,round(10/6,2)
union select '四舍五不入 truncat' ,truncate(10/6,2)
union select '向上取整 ceil',ceil(10/6)
union select '向下取整 floor',floor(10/6);