<select id="statTraffic" resultType="com.nuorui.module.platform.domain.vo.StatTotalVO">
<![CDATA[
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)
GROUP BY dateStr
ORDER BY dateStr DESC;
]]>
</select>
标签：GROUP,SUB,统计数据,查询,dateStr,SQL From： https://www.cnblogs.com/fangts/p/18683385