java - 統計出20170403到20170420期間點擊量前十的廣告每天的點擊量

Question

有一張廣告表advertise(advertise_id,date,count),資料如下：

#我需要統計出某一時段內排名前10的廣告，在這個時間內每天的點擊量。

查詢出來的結果應該是每一天都有10個不同advertise_id的數據，這個sql該怎麼寫？

天蓬老师 · Answer

有個問題是沒出現在記錄中的廣告日期點擊數顯不顯示，如果要顯示的話就需要先構造出這部分數據，不顯示就用下面這個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;

ringa_lee · Answer

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

應該是這樣的吧