博客

查询日期在2017年4月的另一种写法

where substring(orderdate,1,7) = ‘2017-04’

【注意需要查询的数据需

要进行计算且需要连接

时

不必先计算再连接

，可


直接连接后计算


】

（详见1341，1407）

1322. 广告效果

# Write your MySQL query statement below
select ad_id,
        ifnull(round(sum(if(action='Clicked',1,0))*100
        /sum(if(action<>'Ignored',1,0)),2),0) ctr
from Ads
group by ad_id
order by ctr desc ,ad_id asc

1327. 列出指定时间段内所有的下单产品

# Write your MySQL query statement below
select product_name,zongshu unit
from Products P join (
select  product_id,sum(unit) zongshu
from Orders
where order_date between '2020-02-01'and'2020-02-29'
group by product_id
having zongshu >= 100)new_table
#where substring(orderdate,1,7) = '2017-04'
on P.product_id=new_table.product_id

1341. 电影评分

# Write your MySQL query statement below
(select name 'results'
from Users U join( 
select user_id,count(*) zongshu
from MovieRating
group by user_id
)new_table
on new_table.user_id=U.user_id
order by zongshu desc ,name asc limit 1
)
union( 
select title
from Movies M left join (
select movie_id,avg(rating) rnk
from MovieRating
where substring(created_at,1,7)='2020-02'
group by movie_id
)new_new_table
on new_new_table.movie_id=M.movie_id
order by rnk desc, title asc limit 1)

【直接连接两表通过分组直接查出排名（首推下方代码）】

(select u.name as results
from Movie_Rating as m
left join users as u
on m.user_id=u.user_id
group by m.user_id
order by count(*) desc,u.name
limit 1)
union
(select mo.title
from Movie_Rating as m
left join movies as mo
on m.movie_id=mo.movie_id
where m.created_at like "2020-02%"
group by mo.movie_id
order by avg(m.rating) desc,mo.title
limit 1);

1407. 排名靠前的旅行者

【注意需要查询的数据需

要进行计算且需要连接

时

不必先计算再连接

，可


直接连接后计算


】

# Write your MySQL query statement below
select name,ifnull(sum(distance),0) travelled_distance
from Users U left join  Rides R
on R.user_id=U.id
group by R.user_id
order by travelled_distance desc,name

select name,ifnull(zongshu,0) travelled_distance
from Users U left join (
select user_id,sum(distance)zongshu
from Rides
group by user_id
)new_table
on new_table.user_id=U.id
order by travelled_distance desc name