用D3來畫統(tǒng)計圖，數(shù)據(jù)是從mysql數(shù)據(jù)庫中查出來的。mysql

統(tǒng)計圖是關(guān)于某段時間內(nèi)，每一年/每個月/每周的微博數(shù)量的折線圖。數(shù)據(jù)表是一條微博為一條記錄，每條記錄有日期這個字段。sql

首先考慮用group by來統(tǒng)計天天/ 每個月/每一年的微博數(shù)量，可是這樣就會有一個問題：若是某天沒有數(shù)據(jù)，也就是沒有發(fā)微博，那么group by的結(jié)果是沒有這一天的，也就是說咱們group by 的結(jié)果永遠(yuǎn)不會有0。顯然這樣的結(jié)果是不對的。數(shù)據(jù)庫

那么如何解決這個問題呢，思路就是要補(bǔ)全沒有的日期，而后在這個日期對應(yīng)的數(shù)量字段填0。函數(shù)

下面看一下具體的實現(xiàn)。spa

第一步：如何補(bǔ)全沒有的日期3d

最直接的方法就是我手動產(chǎn)生一個連續(xù)的日期。代碼是這樣的：(也是網(wǎng)上參考別人的代碼)code

CREATE TABLE num (i int);

INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select adddate('2012-09-01', numlist.id) as `date` from (SELECT n1.i + n10.i*10 + n100.i*100 AS id FROM num n1 cross join num as n10 cross join num as n100) as numlist where adddate('2012-09-01', numlist.id) <= '2012-09-10';

結(jié)果：

能夠看到產(chǎn)生了指定時間的連續(xù)日期。咱們?nèi)缃褚氖?#xff0c;從某個帳號發(fā)的第一條微博到如今的時間，因此修改相應(yīng)的代碼，成這樣：orm

(對了，講一下，剛開始把num表誤起名為month，因此之后就叫month了)blog

select adddate(( SELECT DATE_FORMAT( wi.PubTime, '%Y-%m-%d') as weibo_date

FROM weibo_info_copy as wi where wi.UID='1705822647' and wi.PubTime > 0

GROUP BY DATE_FORMAT( wi.PubTime, '%Y-%m-%d' )

limit 1), numlist.id) as `date` from (SELECT n1.i + n10.i*10 + n100.i*100+n1000.i*1000 AS id FROM month n1 cross join month as n10 cross join month as n100 cross join month as n1000) as numlist where adddate(( SELECT DATE_FORMAT( wi.PubTime, '%Y-%m-%d') as weibo_date

FROM weibo_info_copy as wi where wi.UID='1705822647'

GROUP BY DATE_FORMAT( wi.PubTime, '%Y-%m-%d' )

limit 1), numlist.id) <= CURRENT_DATE

修改了三處：

(1)起始時間：這里用了一個sql語句的查詢結(jié)果，就是微博數(shù)據(jù)的第一條的時間。這樣防止了不一樣帳號相同的起始時間反映在圖表上會形成很長的空白或者是漏掉一部分?jǐn)?shù)據(jù)。it

(2)終止時間：這里選取當(dāng)前時間

(3)范圍：“注意到以前的數(shù)量只會到1000，這里多加了一部分，數(shù)量會擴(kuò)大到10000，算了一下27年有余，應(yīng)該是夠用了。

這樣就能夠產(chǎn)生一個連續(xù)的日期：

能夠看到，從2010-03-06到如今，有2190天。

好了，如今咱們完成了第一步，獲得了連續(xù)的時間。

第二步：將結(jié)果存入表格，以便聯(lián)合查詢。

這里用到insert into語句。

drop table if exists weibo_line_date;

CREATE TABLE if not exists weibo_line_date (name date);

insert into weibo_line_date (name) select * from (

select adddate(( SELECT DATE_FORMAT( wi.PubTime, '%Y-%m-%d') as weibo_date

FROM weibo_info_copy as wi where wi.UID='1705822647' and wi.PubTime > 0

GROUP BY DATE_FORMAT( wi.PubTime, '%Y-%m-%d' )

limit 1), numlist.id) as `date` from (SELECT n1.i + n10.i*10 + n100.i*100+n1000.i*1000 AS id FROM month n1 cross join month as n10 cross join month as n100 cross join month as n1000) as numlist where adddate(( SELECT DATE_FORMAT( wi.PubTime, '%Y-%m-%d') as weibo_date

FROM weibo_info_copy as wi where wi.UID='1705822647'

GROUP BY DATE_FORMAT( wi.PubTime, '%Y-%m-%d' )

limit 1), numlist.id) <= CURRENT_DATE

) as t

注意到兩點(diǎn)：

(1)先要刪除原來的表，為了避免重復(fù)添加數(shù)據(jù)

(2)在建表時指定數(shù)據(jù)類型和字符集，否則會出錯。

好了，如今能夠獲得一個如上結(jié)果的新表，叫作weibo_line_date。

第三步：聯(lián)合查詢，無數(shù)據(jù)補(bǔ)0

SELECT weibo_line_date.name as weibo_date, IF(weibo_num IS NULL , 0, weibo_num) as weibo_num

FROM(SELECT DATE_FORMAT( wi.PubTime, '%Y-%m-%d') as weibo_date , COUNT(*) as weibo_num

FROM weibo_info_copy as wi where wi.UID='1705822647'

GROUP BY DATE_FORMAT( wi.PubTime, '%Y-%m-%d' )) t

RIGHT JOIN weibo_line_date on t.weibo_date = weibo_line_date.name

結(jié)果：

這就獲得了我想要的結(jié)果。

看一下圖吧

這樣就完成了咱們的需求。

拓展一下，若是查每一年/每個月的，又該怎么辦呢。

首先想到，咱們要產(chǎn)生一個連續(xù)的月份的表。修改代碼：

select date_format(date_add((SELECT DATE_FORMAT( wi.PubTime, '%Y-%m-%d') as weibo_date

FROM weibo_info_copy as wi where wi.UID='1705822647'

GROUP BY DATE_FORMAT( wi.PubTime, '%Y-%m' )

limit 1), interval numlist.id month),'%Y-%m') as `date`

from (SELECT n1.i + n10.i*10 + n100.i*100 AS id FROM month n1 cross join month as n10 cross join month as n100) as numlist

where date_format(date_add((SELECT DATE_FORMAT( wi.PubTime, '%Y-%m-%d') as weibo_date

FROM weibo_info_copy as wi where wi.UID='1705822647'

GROUP BY DATE_FORMAT( wi.PubTime, '%Y-%m' )

limit 1), interval numlist.id month),'%Y-%m') <= date_format(CURRENT_DATE,'%Y-%m')

注意兩個地方：

(1)數(shù)據(jù)格式：咱們?nèi)缃褚氖?010-03這樣的數(shù)據(jù)，因此要用data_format函數(shù)，將‘0000-00-00‘的數(shù)據(jù)轉(zhuǎn)換為’0000-00‘

(2)間隔：date_add函數(shù)，第一個參數(shù)是起始時間；第二個參數(shù)是間隔，若是為整數(shù)，就默認(rèn)為間隔一天，如今咱們要設(shè)置為一個月，

interval numlist.id month要寫成這樣。若是是一年就是year

其次，在新建表的時候，若是指定字段類型為date會出錯，結(jié)果全為’0000-00‘，因此我改成了text。

這樣的話，在下一步聯(lián)合查詢的時候，字符集不匹配會報錯，因此要指定相應(yīng)的字符集。這里必定要注意。

CREATE TABLE if not exists weibo_line_date (name text CHARACTER SET UTF8)

接下來的步驟就和以前的同樣了。

總結(jié)

以上是生活随笔為你收集整理的mysql 怎么查询结果补0_mysql查询连续时间数据——无数据补0的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。