leetcode 1341 电影评分
( select u1.name as results from Users u1 left join( select mr1.user_id, count(mr1.rating) as c1 from MovieRating as mr1 group by mr1.user_id having c1 = ( select max(p.c2) from Users as u2 left join ( select mr2.user_id, count(mr2.rating) as c2 from MovieRating as mr2 group by mr2.user_id ) as p on u2.user_id = p.user_id )) p2 on u1.user_id = p2.user_id where p2.user_id is not null order by u1.name asc limit 0,1 ) union all ( select m.title as results from Movies m left join ( select mr4.movie_id, avg(mr4.rating) as d1 from MovieRating mr4 where EXTRACT(MONTH FROM mr4.created_at) = '02' and EXTRACT(YEAR FROM mr4.created_at) = '2020' group by mr4.movie_id having round(d1,2) = ( select round(max(p3.d2),2) from ( select mr3.movie_id, avg(mr3.rating) as d2 from MovieRating mr3 where EXTRACT(MONTH FROM mr3.created_at) = '02' and EXTRACT(YEAR FROM mr3.created_at) = '2020' group by mr3.movie_id ) p3 ) ) p4 on m.movie_id = p4.movie_id where p4.movie_id is not null order by m.title asc limit 0,1 )
==
( select u.name as results from Users u left join( select mr1.user_id, count(mr1.rating) as umr from MovieRating as mr1 group by mr1.user_id ) p1 on u.user_id = p1.user_id where p1.user_id is not null order by p1.umr desc, u.name asc limit 0,1 ) union all ( select m.title as results from Movies m left join ( select mr2.movie_id, avg(mr2.rating) as mmr from MovieRating mr2 where EXTRACT(MONTH FROM mr2.created_at) = '02' and EXTRACT(YEAR FROM mr2.created_at) = '2020' group by mr2.movie_id ) p2 on m.movie_id = p2.movie_id where p2.movie_id is not null order by p2.mmr desc, m.title asc limit 0,1 )
==
标签:评分,1341,mr1,mr2,id,user,movie,leetcode,select From: https://www.cnblogs.com/carlzhang19/p/17451402.html