sql
all any some
All:对所有数据都满足条件,整个条件才成立 ,例如:5000大于所有返回的薪水
Any:只要有一条数据满足条件,整个条件成立,例如,5000大于薪水中的3000,10000,7000
Some的作用和Any一样 。
select order_id
from OrdersDetails
group by order_id
having max(quantity) >
all(select avg(quantity) from OrdersDetails group by order_id)
avg
#统计date不为null的数据占比
avg(date is not null)
等价于
sum(if(date is not null,1,0))/count(*)
select round(sum(if(a.event_date is not null, 1, 0))/count(*), 2) fraction
from
(select player_id, min(event_date) as login
from activity
group by player_id) p
left join activity a
on p.player_id=a.player_id and datediff(a.event_date, p.login)=1
datediff()
这里注意!!DATEDIFF()
两个参数与三个参数的时候startdate
和enddate
前后顺序是相反的
DATEDIFF(datepart,startdate,enddate)
DATEDIFF(enddate,startdate)