mysql查詢最近10天的數(shù)據(jù)，沒有數(shù)據(jù)自動補0; 正常的數(shù)據(jù)庫中的表, 某一天如果沒有數(shù)據(jù)的話, 以ctime字段為基準進行數(shù)據(jù)統(tǒng)計就會出現(xiàn)訂單量為0的統(tǒng)計不到數(shù)據(jù);
原網(wǎng)址：https://blog.csdn.net/sq_com/article/details/84997563
解決方法:
- 想方設法的創(chuàng)建一張?zhí)摂M的數(shù)據(jù)表, 專門表示日期.如下:
SELECT
tbl._date AS _date,
IFNULL(tbr.totalnum, 0) AS totalnum
FROM
(
SELECT
@s :=@s + 1 AS _index,
DATE(
DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)
) AS _date
FROM
information_schema.tables,
# mysql.help_topic,
# 關聯(lián)任意一張系統(tǒng)表; 一定的是系統(tǒng)表;
(SELECT @s := - 1) temp
WHERE
@s < 10
ORDER BY
_date
) AS tbl
LEFT JOIN (
SELECT count(*) AS totalnum, DATE(ctime) createdate
FROM zxx_news GROUP BY createdate ) AS tbr ON tbl._date = tbr.createdate
GROUP BY tbl._date ;
圖片.png
2.在做報表數(shù)據(jù)統(tǒng)計的時候欺税，用一個折線圖用來顯示并統(tǒng)計最近 6 個月內(nèi)的數(shù)據(jù)結算情況侈沪。為了前端小伙伴方便處理數(shù)據(jù)，需要將沒有數(shù)據(jù)的月份默認填充為 0
SELECT
CONVERT (t2.year_month_str, CHAR) `timeStamp`,
IFNULL(SUM(i.id), 0) AS total
FROM
(
SELECT
@rownum :=@rownum + 1 AS num,
date_format(
DATE_SUB(now(), INTERVAL @rownum MONTH),
'%Y/%m'
) AS year_month_str
FROM
(SELECT @rownum := - 1) AS r_init,
(
SELECT
c.id
FROM
zxx_news c
LIMIT 6
) AS c_init
) t2
LEFT JOIN zxx_news AS i ON (
CONCAT(
DATE_FORMAT(i.ctime, '%Y'),
'/',
DATE_FORMAT(i.ctime, '%m')
) = t2.year_month_str
AND i.id > 100
)
GROUP BY
t2.year_month_str
圖片.png
