mysql查詢最近10天的數(shù)據(jù)，沒(méi)有數(shù)據(jù)自動(dòng)補(bǔ)0; 正常的數(shù)據(jù)庫(kù)中的表, 某一天如果沒(méi)有數(shù)據(jù)的話, 以ctime字段為基準(zhǔn)進(jìn)行數(shù)據(jù)統(tǒng)計(jì)就會(huì)出現(xiàn)訂單量為0的統(tǒng)計(jì)不到數(shù)據(jù);
原網(wǎng)址：https://blog.csdn.net/sq_com/article/details/84997563

解決方法:

1. 想方設(shè)法的創(chuàng)建一張?zhí)摂M的數(shù)據(jù)表, 專門(mén)表示日期.如下:

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,
            # 關(guān)聯(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.在做報(bào)表數(shù)據(jù)統(tǒng)計(jì)的時(shí)候，用一個(gè)折線圖用來(lái)顯示并統(tǒng)計(jì)最近 6 個(gè)月內(nèi)的數(shù)據(jù)結(jié)算情況。為了前端小伙伴方便處理數(shù)據(jù)，需要將沒(méi)有數(shù)據(jù)的月份默認(rèn)填充為 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