mysql面试题

有一张product表如下，请查询每个城市买的最好的商品的good_id.

select city,good_id
  from
	(select  city,good_id,cnt,max(cnt) over(PARTITION by city order by cnt desc ) as maxcnt
			from 
				(select distinct city, good_id, count(good_id) over(PARTITION by city,good_id  ) as cnt
					from product) as t
	) as l where l.cnt=l.maxcnt	

select a.good_id,a.city,t.cnt from 
  (select good_id,city,count(good_id) as cnt from product GROUP BY good_id,city) as a 
	  join
		 (select city,max(cnt) as cnt from 
			 (select good_id,city,count(good_id) as cnt from product GROUP BY good_id,city) as b group by city ) as t
	on a.cnt = t.cnt and a.city = t.city

知识点：

创建如下表，找up主平均上传视频时间大于300的video_id,且按照平均时间降序，video_id升序，返回video_id的3-6行

CREATE TABLE uploader_video_record (
    video_id BIGINT PRIMARY KEY,
    uploader_id BIGINT,
    video_duration int
);

INSERT INTO uploader_video_record (video_id, uploader_id, video_duration)
VALUES
 (1, 101, 60),
(2, 102, 600),
(3, 103, 310),
(4, 101, 120),
(5, 104, 3200),
(6, 102, 330),
(7, 103, 290),
(8, 105, 290),
(9, 101, 180),
(10, 103, 320);

答案：

select video_id from uploader_video_record as a join
(select uploader_id,duration from 
		(select uploader_id,avg(video_duration) as duration from uploader_video_record GROUP BY uploader_id ) as t
		where t.duration>300) as b 
		on a.uploader_id=b.uploader_id order by b.duration desc, a.video_id asc limit 2,4;