mysql常用语法整理

最近在做一些关于统计方面的开发，整理了以下关于各个时间段的查询

查询当天内各时段的XX统计(比如小时客流量)

说明
表名：cg_event_tendency_num_hour_statistics
数据库天字段：event_date
数据库时间字段：event_hour ==> 返回给前端as为name
数据库当前时间段数量字段：event_num ==> 返回给前端as为value

SELECT event_hour,SUM( event_num ) as event_num  FROM cg_event_tendency_num_hour_statistics WHERE date_format(event_date, '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d') GROUP BY event_hour

这里会出现一个问题，数据查询出来的可能不是完整的时间段

但是我们想返回给前端完整的时间00-23该怎么操作呢？
我们可以使用LEFT JOIN来进行操作，先使用 UNION ALL查询出00-23的全部时间,再将真实数据进行左关联

完整sql如下：

SELECT timetable.HOUR AS NAME, ifnull(sumtable.count, 0) AS VALUE
FROM (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT 1 AS HOUR
    UNION ALL
    SELECT 2 AS HOUR
    UNION ALL
    SELECT 3 AS HOUR
    UNION ALL
    SELECT 4 AS HOUR
    UNION ALL
    SELECT 5 AS HOUR
    UNION ALL
    SELECT 6 AS HOUR
    UNION ALL
    SELECT 7 AS HOUR
    UNION ALL
    SELECT 8 AS HOUR
    UNION ALL
    SELECT 9 AS HOUR
    UNION ALL
    SELECT 10 AS HOUR
    UNION ALL
    SELECT 11 AS HOUR
    UNION ALL
    SELECT 12 AS HOUR
    UNION ALL
    SELECT 13 AS HOUR
    UNION ALL
    SELECT 14 AS HOUR
    UNION ALL
    SELECT 15 AS HOUR
    UNION ALL
    SELECT 16 AS HOUR
    UNION ALL
    SELECT 17 AS HOUR
    UNION ALL
    SELECT 18 AS HOUR
    UNION ALL
    SELECT 19 AS HOUR
    UNION ALL
    SELECT 20 AS HOUR
    UNION ALL
    SELECT 21 AS HOUR
    UNION ALL
    SELECT 22 AS HOUR
    UNION ALL
    SELECT 23 AS HOUR
) timetable
    LEFT JOIN (
        SELECT event_hour AS HOUR, SUM(event_num) AS count
        FROM cg_event_tendency_num_hour_statistics
        WHERE date_format(event_date, '%Y-%m-%d') =  DATE_FORMAT(now(), '%Y-%m-%d')
        GROUP BY HOUR
    ) sumtable
    ON timetable.HOUR = sumtable.HOUR
ORDER BY NAME

结果：

查询当月内每天的XX统计(比如日客流量)

说明
表名：cg_event_tendency_num_statistics
数据库月字段：event_month
数据库天字段：event_date ==> 返回给前端as为name
数据库当前时间段数量字段：event_num ==> 返回给前端as为value

SELECT event_date , SUM( event_num ) as event_num FROM cg_event_tendency_num_statistics WHERE event_month = DATE_FORMAT( now(), '%Y-%m' ) GROUP BY event_date

这里也是同样的问题，数据查询出来的可能不是完整的日期段

处理方式和小时的同理,只是这里采用方法生成了每月的天数

SELECT DAY(timetable.date) AS NAME, ifnull(sumtable.count, 0) AS VALUE
FROM (
    SELECT date, count
    FROM (
        SELECT DATE_SUB(last_day(curdate()), INTERVAL xc - 1 DAY) AS date, 0 AS count
        FROM (
            SELECT @xi := @xi + 1 AS xc
            FROM (
                SELECT 1
                UNION
                SELECT 2
                UNION
                SELECT 3
                UNION
                SELECT 4
                UNION
                SELECT 5
                UNION
                SELECT 6
            ) xc1, (
                    SELECT 1
                    UNION
                    SELECT 2
                    UNION
                    SELECT 3
                    UNION
                    SELECT 4
                    UNION
                    SELECT 5
                    UNION
                    SELECT 6
                ) xc2, (
                    SELECT @xi := 0
                ) xc0
        ) xcxc
    ) x0
    WHERE x0.date >= (
        SELECT date_add(curdate(), INTERVAL -DAY(curdate()) + 1 DAY)
    )
    ORDER BY date
) timetable
    LEFT JOIN (
        SELECT event_date AS date, SUM(event_num) AS count
        FROM cg_event_tendency_num_statistics
        WHERE event_month = DATE_FORMAT(now(), '%Y-%m')
        GROUP BY date
    ) sumtable
    ON timetable.date = sumtable.date
ORDER BY NAME

结果：

年客流量的方式和时间的差不多，这里就不整理了