有一张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;