MySql查询本周、本月、本年数据（没有数据则补全0）

最近写项目，发现有很多图表统计，需要查询本周、本月、本年数据。但是图表需要两个数组，一个日期数组，一个数据数组。然而数据库查询却只能查询出有数据的日期数据，所以找了很多资料终于能有补全日期和数据的方法。这样查询sql性能可能不太好，慎重使用~~。有更好的建议或者优化欢迎各位大神留言~不多说直接贴SQL语句

–查询本周数据（在国内是周一至周日，在国外是周日至周六。所以这里按国内来）

SELECT
    CASE datas.weeks
WHEN '1' THEN
    '星期一'
WHEN '2' THEN
    '星期二'
WHEN '3' THEN
    '星期三'
WHEN '4' THEN
    '星期四'
WHEN '5' THEN
    '星期五'
WHEN '6' THEN
    '星期六'
WHEN '0' THEN
    '星期日'
END AS 'week',
 IFNULL(datas.totalUser, 0) totalUser
FROM
    (
        SELECT
            a.d weeks,
            b.totalUser
        FROM
            (
                SELECT
                    0 AS d
                UNION ALL
                    SELECT
                        1
                    UNION ALL
                        SELECT
                            2
                        UNION ALL
                            SELECT
                                3
                            UNION ALL
                                SELECT
                                    4
                                UNION ALL
                                    SELECT
                                        5
                                    UNION ALL
                                        SELECT
                                            6
            ) a
        LEFT JOIN (
            SELECT
                date_format(us.ctime, '%w') AS datetime,
                COUNT(us.usersId) AS totalUser
            FROM
                users us
            WHERE
                yearweek(now() - INTERVAL 1 DAY) = yearweek(ctime - INTERVAL 1 DAY)
            GROUP BY
                date_format(us.ctime, '%w')
        ) b ON a.d = b.datetime
    ) datas
ORDER BY
    datas.weeks ASC

–查询本月数据（按天数查询）

SELECT a.d ctime,IFNULL(b.totalUser,0)totalUser FROM (SELECT ADDDATE(y.first, x.d - 1) as d
FROM
(
SELECT 1 AS d UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 20 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
SELECT 23 UNION ALL
SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 26 UNION ALL
SELECT 27 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION ALL
SELECT 31
) x,
(
SELECT date(now()) - INTERVAL DAY(date(now())) - 1 DAY AS first, 
DAY(LAST_DAY(date(now()))) AS last) y
WHERE x.d <= y.last)a left join (
            select date(us.ctime) as datetime, COUNT(us.usersId) as totalUser
            from users us  group by date(us.ctime)
            ) b on a.d = b.datetime ORDER BY date(a.d) asc

查询本年数据（按月份查询）

SELECT
    CASE datas.weeks
WHEN '1' THEN
    '一月'
WHEN '2' THEN
    '二月'
WHEN '3' THEN
    '三月'
WHEN '4' THEN
    '四月'
WHEN '5' THEN
    '五月'
WHEN '6' THEN
    '六月'
WHEN '7' THEN
    '七月'
WHEN '8' THEN
    '八月'
WHEN '9' THEN
    '九月'
WHEN '10' THEN
    '十月'
WHEN '11' THEN
    '十一月'
WHEN '12' THEN
    '十二月'
END AS 'week',
 IFNULL(datas.totalUser, 0) totalUser
FROM
    (
        SELECT
            a.d weeks,
            b.totalUser
        FROM
            (
                    SELECT
                        1 AS d
                    UNION ALL
                        SELECT
                            2
                        UNION ALL
                            SELECT
                                3
                            UNION ALL
                                SELECT
                                    4
                                UNION ALL
                                    SELECT
                                        5
                                    UNION ALL
                                        SELECT
                                            6 UNION ALL
                                        SELECT
                                            7 UNION ALL
                                        SELECT
                                            8 UNION ALL
                                        SELECT
                                            9 UNION ALL
                                        SELECT
                                            10 UNION ALL
                                        SELECT
                                            11 UNION ALL
                                        SELECT
                                            12
            ) a
        LEFT JOIN (
            SELECT
                date_format(us.ctime, '%m') AS datetime,
                COUNT(us.usersId) AS totalUser
            FROM
                users us
            WHERE
                YEAR(us.ctime)=YEAR(NOW())
            GROUP BY
                datetime
        ) b ON a.d = b.datetime
    ) datas
ORDER BY
    datas.weeks ASC