java - 统计出20170403到20170420期间点击量前十的广告每天的点击量

Question

有一张广告表advertise(advertise_id,date,count),数据如下：

我需要统计出某一时间段内排名前10的广告，在这个时间段内每天的点击量。

查询出来的结果应该是每一天都有10条不同advertise_id的数据，这个sql应该怎么写？

Answer 1

有个问题是没出现在记录中的广告日期点击数显不显示，如果要显示的话就需要先构造出这部分数据，不显示就用下面这个sql就好

select a.* from advertise a join (select advertise_id,sum(count) sm from advertise group by advertise_id order by sm desc limit 10) b on a.advertise_id=b.advertise_id where a.date BETWEEN 20170403 AND 20170420 order by a.date,a.count;

Answer 2

SELECT s.date,s.advertise_id,s.count FROM advertise_stat s
WHERE
    EXISTS (
        SELECT advertise_id FROM (SELECT advertise_id FROM advertise_stat GROUP BY advertise_id ORDER BY count DESC LIMIT 10) AS advertise_temp
        WHERE advertise_id = s.advertise_id
    )
AND s.date BETWEEN 20170403 AND 20170420
ORDER BY s.date ASC,s.count DESC

应该是这样的吧