- 上升的温度
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()
- 每台机器的平均进程运行时间
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
- 学生们参加各科测试的次数
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)
- 至少有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
- 确认率
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);
- 有趣的电影
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
- 平均售价
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