問題描述

有一張廣告表advertise(advertise_id,date,count),數(shù)據(jù)如下：

我需要統(tǒng)計(jì)出某一時(shí)間段內(nèi)排名前10的廣告，在這個(gè)時(shí)間段內(nèi)每天的點(diǎn)擊量。

查詢出來的結(jié)果應(yīng)該是每一天都有10條不同advertise_id的數(shù)據(jù)，這個(gè)sql應(yīng)該怎么寫？

問題解答

有個(gè)問題是沒出現(xiàn)在記錄中的廣告日期點(diǎn)擊數(shù)顯不顯示，如果要顯示的話就需要先構(gòu)造出這部分?jǐn)?shù)據(jù)，不顯示就用下面這個(gè)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;回答2：

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

應(yīng)該是這樣的吧