SQL查询最近的年、月、周、日的统计数据

<select id="statTraffic" resultType="com.nuorui.module.platform.domain.vo.StatTotalVO">
    SELECT
      CASE
        WHEN #{dateType} = 0 THEN YEAR(date_series.generated_date)  -- 按年查询
        WHEN #{dateType} = 1 THEN DATE_FORMAT(date_series.generated_date, '%Y-%m')  -- 按月查询
        WHEN #{dateType} = 2 THEN CONCAT(YEAR(date_series.generated_date), '-', LPAD(WEEK(date_series.generated_date), 2, '0'))  -- 按周查询
        WHEN #{dateType} = 3 THEN DATE_FORMAT(date_series.generated_date, '%Y-%m-%d')  -- 按日查询
        ELSE DATE_FORMAT(date_series.generated_date, '%Y-%m-%d')
      END AS dateStr,
      SUM(CASE WHEN p.direction = 1 THEN 1 ELSE 0 END) AS upTotal,
      SUM(CASE WHEN p.direction = 0 THEN 1 ELSE 0 END) AS downTotal
    FROM (
      SELECT
        CASE
          WHEN #{dateType} = 0 THEN DATE_SUB(CURDATE(), INTERVAL (n.n - 1) YEAR)  -- 按年查询
          WHEN #{dateType} = 1 THEN DATE_SUB(CURDATE(), INTERVAL (n.n - 1) MONTH)  -- 按月查询
          WHEN #{dateType} = 2 THEN DATE_SUB(CURDATE(), INTERVAL (n.n - 1) WEEK)  -- 按周查询
          WHEN #{dateType} = 3 THEN DATE_SUB(CURDATE(), INTERVAL (n.n - 1) DAY)  -- 按日查询
          ELSE DATE_SUB(CURDATE(), INTERVAL (n.n - 1) DAY)
        END AS generated_date
      FROM (
        SELECT @rownum := @rownum + 1 AS n
        FROM (SELECT 1 FROM information_schema.columns LIMIT #{limit}) a, (SELECT 1) b, (SELECT @rownum := 0) c
      ) n
    ) date_series
    LEFT JOIN plat_vessel_traffic p
      ON (
        (YEAR(p.data_time) = YEAR(date_series.generated_date) AND #{dateType} = 0)  -- 按年查询
        OR (DATE_FORMAT(p.data_time, '%Y-%m') = DATE_FORMAT(date_series.generated_date, '%Y-%m') AND #{dateType} = 1)  -- 按月查询
        OR (YEAR(p.data_time) = YEAR(date_series.generated_date) AND WEEK(p.data_time) = WEEK(date_series.generated_date) AND #{dateType} = 2)  -- 按周查询
        OR (DATE_FORMAT(p.data_time, '%Y-%m-%d') = DATE_FORMAT(date_series.generated_date, '%Y-%m-%d') AND #{dateType} = 3)  -- 按日查询
      )
      AND p.data_time >= DATE_SUB(CURDATE(), INTERVAL 365 DAY)
    <if test="startTime != null">
        and p.data_time >= #{startTime}
    </if>
    <if test="endTime != null">
        and p.data_time &lt;= #{endTime}
    </if>
    GROUP BY dateStr
    ORDER BY dateStr DESC;
</select>