leetcode刷题SQL--day01

1. 上升的温度

select w.id
from weather w
join weather w2
    on w.recordDate = Date_Add(w2.recordDate,interval 1 day)
where w.temperature > w2.temperature

1.自连接
2.Date_Add()

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

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

1.自连接
2.round(,3)
3.avg() —聚合函数->分组查询*
聚合函数有 avg/max/min/sum/count

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

select st.student_id,st.student_name,su.subject_name,ifnull(grouped.attended_exams,0) attended_exams
from students st
join subjects su 
left join(
    select student_id,subject_name,count(*) as attended_exams
    from examinations
    group by student_id,subject_name
) grouped
on st.student_id = grouped.student_id and su.subject_name = grouped.subject_name
order by st.student_id,su.subject_name

inner join：理解为“有效连接”，两张表中都有的数据才会显示
left join：理解为“有左显示”，比如on a.field=b.field，则显示a表中存在的全部数据及a、b中都有的数据，a中有、b中没有的数据以null显示
right join：理解为“有右显示”，比如on a.field=b.field，则显示b表中存在的全部数据及a、b中都有的数据，b中有、a中没有的数据以null显示
full join：理解为“全连接”，两张表中所有数据都显示，实际就是inner +(left-inner)+(right-inner)

4. 至少有5名直接下属的经理 🤦‍♀️

select Manager.Name as Name
from
Employee as Manager join Employee as Report
on Manager.Id = Report.ManagerId
group by Manager.Id
having count(Report.Id) >= 5

1.Every derived table must have its own alias
2.where 和having的区别
以下是having和where的区别：
Select city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
作用的对象不同。WHERE 子句作用于表和视图，HAVING 子句作用于组。
WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而 HAVING 在分组和聚集之后选取分组的行。因此，WHERE 子句不能包含聚集函数； 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。 相反，HAVING 子句总是包含聚集函数。（严格说来，你可以写不使用聚集的 HAVING 子句， 但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。）
在前面的例子里，我们可以在 WHERE 里应用城市名称限制，因为它不需要聚集。 这样比在 HAVING 里增加限制更加高效，因为我们避免了为那些未通过 WHERE 检查的行进行分组和聚集计算
综上所述：
having一般跟在group by之后，执行记录组选择的一部分来工作的。
where则是执行所有数据来工作的。
再者having可以用聚合函数，如having sum(qty)>1000

5. 确认率

select s.user_id,round(ifnull(avg(c.action='confirmed'),0),2)
from signedup s
left join confirmations c
on s.user_id=c.userid
group by s.user_id

avg()
SELECT site_id, count FROM access_logWHERE count > (SELECT AVG(count) FROM
access_log);

6. 有趣的电影

select * from cinema
where description<>'boring' and id&1
--where description!='boring' and mod(id,2)=1
order by rating desc;

得到奇数
1.取模 mod(a,b) a%b
2.与运算 a&1true

7. 平均售价

select product_id,ifnull(round(sum(sales)/sum(units),2),0) as average_price
from(
    select 
        prices.product_id as product_id,
        prices.price * unitsSold.units as sales,
        unitsSold.units as units
    from prices
    left join unitsSold on prices.product_id=unitsSold.product_id
    and (unitsSold.purchase_date between prices.start_date and prices.end_date) 
)t
group by product_id

between and的边界问题