Mysql查询统计最近12个月，每个月的数量，并且显示每个月份，如果月份没有数据量需要以“0”填充

第一种：

可以使用MySQL的日期和时间函数来生成包含最近12个月的日期，并且使用LEFT JOIN 和 GROUP BY 语句来统计每个月的数量。下面是一个示例查询：

SELECT 
    DATE_FORMAT(date_range.month, '%Y-%m') AS month,
    COUNT(your_table.id) AS count
FROM 
    (SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS month
     FROM 
        (SELECT 0 AS a UNION ALL SELECT 1 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) AS a
     CROSS JOIN 
        (SELECT 0 AS a UNION ALL SELECT 1 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) AS b
     CROSS JOIN 
        (SELECT 0 AS a UNION ALL SELECT 1 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) AS c
    ) AS date_range
LEFT JOIN 
    your_table ON DATE_FORMAT(date_range.month, '%Y-%m') = DATE_FORMAT(your_table.date_column, '%Y-%m')
WHERE 
    date_range.month >= DATE_FORMAT(CURDATE() - INTERVAL 11 MONTH, '%Y-%m')
GROUP BY 
    month
ORDER BY 
    month;

请注意以下几点：

1. your_table 是你的数据表名，date_column 是包含日期信息的列名。
2. 此查询首先生成最近12个月的日期范围，然后使用 LEFT JOIN 将这些日期范围与你的表连接。LEFT JOIN 确保即使某些月份没有匹配的数据，也会将其列出。
3. 使用 DATE_FORMAT 函数来提取月份和年份，并将结果格式化为 “YYYY-MM”。
4. 如果你的数据表中没有数据的月份，它将在结果中显示为零。

第二种：

可以通过编写带有 CASE 语句的查询来实现这个目标。 CASE 语句可以帮助您检查每个月份是否有数据，并返回对应的数量。以下是一个示例：

SELECT
  month_numbers.month_number,
  SUM(CASE WHEN MONTH(your_table.date_column) = month_numbers.month_number THEN 1 ELSE 0 END) AS month_count
FROM
  (SELECT 1 AS month_number
  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) AS month_numbers
LEFT JOIN 
  your_table
ON 
  MONTH(your_table.date_column) = month_numbers.month_number
WHERE 
  your_table.date_column >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY 
  month_numbers.month_number
ORDER BY 
  month_numbers.month_number;

请注意，您仍然需要将 your_table 替换为您的实际表名，并将 date_column 替换为您的实际日期列名称。这个查询不会创建新的表，而是使用子查询来生成一个包含1到12个月的临时表。

第三种：

SELECT a.click_month,IFNULL(b.READCOUNT,0) AS READCOUNT
FROM ( 

	SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH),'%Y-%m') AS click_month 
    UNION ALL
	SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 11 MONTH),'%Y-%m') AS click_month 
    UNION ALL
	SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 10 MONTH),'%Y-%m') AS click_month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 9 MONTH),'%Y-%m') AS click_month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 8 MONTH),'%Y-%m') AS click_month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 7 MONTH),'%Y-%m') AS click_month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH),'%Y-%m') AS click_month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH),'%Y-%m') AS click_month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH),'%Y-%m') AS click_month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH),'%Y-%m') AS click_month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH),'%Y-%m') AS click_month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m') AS click_month 
    UNION ALL 
    SELECT DATE_FORMAT(CURDATE(),'%Y-%m') AS click_month 
    ) a LEFT JOIN ( 
    SELECT DATE_FORMAT(borrow_time,'%Y-%m') AS DATETIME, count(id) AS READCOUNT
    FROM archive_borrow_apply 
    WHERE archive_company_id=1645 and del=false and status in('4','6' ,'7')
    GROUP BY DATE_FORMAT(borrow_time,'%Y-%m') 
    
) b ON a.click_month = b.datetime
order by a.click_month 

archive_borrow_apply ：你的表名

borrow_time：表中的时间字段

第四种：MySQL 5.8以下 是不支持 WITH RECURSIVE 的

可以使用MySQL中的 WITH RECURSIVE 来生成一个数字序列，然后将其与您的数据表进行连接。以下是一个示例查询：

WITH RECURSIVE month_numbers AS (
  SELECT 1 AS month_number
  UNION ALL
  SELECT month_number + 1 FROM month_numbers WHERE month_number < 12
)
SELECT
  month_numbers.month_number,
  IFNULL(COUNT(your_table.id), 0) AS month_count
FROM
  month_numbers
LEFT JOIN 
  your_table
ON 
  MONTH(your_table.date_column) = month_numbers.month_number
  AND your_table.date_column >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY 
  month_numbers.month_number
ORDER BY 
  month_numbers.month_number;

这个查询使用 WITH RECURSIVE 构建了一个包含数字序列的临时表 month_numbers，然后将其与您的数据表进行连接。在 LEFT JOIN 子句中，它还限制了日期范围以仅包括最近的12个月数据。最后，使用 IFNULL 函数来处理空值，如果某个月份没有数据，则将数量设置为零。

这种方法与之前的方法相比，使查询更加紧凑和易读，而不需要显式列举数字。

第五种：（推荐）

SELECT
        time_series.time_point AS date,
        any_value(COALESCE(your_table .tem, 0)) AS tem,
        any_value(COALESCE(your_table .hum, 0)) AS hum,
        any_value(COALESCE(your_table .pm25, 0)) AS pm25,
        any_value(COALESCE(your_table .tvoc, 0)) AS tvoc,
        any_value(COALESCE(MAX(your_table .create_time), 0)) AS dataInfo
        FROM (
        SELECT
        CURDATE() + INTERVAL (10 - n) HOUR AS time_point
        FROM
        (SELECT 0 AS n
        UNION SELECT 1
        UNION SELECT 2
        UNION SELECT 3
        UNION SELECT 4
        UNION SELECT 5
        UNION SELECT 6
        UNION SELECT 7
        UNION SELECT 8
        UNION SELECT 9
        UNION SELECT 10
        UNION SELECT 11
        ) AS hours
        ) AS time_series
        LEFT JOIN your_table  ON your_table .create_time < time_series.time_point
        and your_table .create_time >= DATE_SUB(time_series.time_point, interval 1 hour)
        
        GROUP BY
        time_series.time_point
        ORDER BY
        time_series.time_point ASC;