时间戳

首页 > 解决方案 > MySQL在结果集中添加缺少的月份

mysql - MySQL在结果集中添加缺少的月份

问题描述

我正在尝试在此结果集中添加缺失的月份。

如果缺少月份，请将其添加为Quantita的值 0 。

SELECT MONTH(Data) AS Mese,Count(*) AS Quantita 
FROM prenotazioni 
WHERE Cancellata IS NULL 
AND FKCampo = 1 
AND YEAR(Data) = YEAR(CURDATE()) -1 
GROUP BY Mese 
ORDER BY Mese ASC 


+------+----------+
| Mese | Quantita |
+------+----------+
| 4    | 123      |
+------+----------+
| 5    | 100      |
+------+----------+
| 7    | 377      |
+------+----------+
| 9    | 54       |
+------+----------+

标签： mysqlsql

解决方案

以下是混乱的，我相信必须有一种更清洁的方式......

所以，首先我创建一个包含所有月份的表：

CREATE TABLE all_months (
 month_num INT
);

INSERT INTO all_months VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

然后我将您的查询与此表（在我的情况下在右侧）连接起来，并使用 case-when 来确定是否有计数：

SELECT 
  all_months.month_num, 
  CASE 
    WHEN tmp.Quantita is NULL THEN 0 
    ELSE tmp.Quantita 
  END as Quantita
FROM (
  SELECT MONTH(Data) AS Mese,Count(*) AS Quantita 
  FROM prenotazioni 
  WHERE Cancellata IS NULL 
  AND FKCampo = 1 
  AND YEAR(Data) = YEAR(CURDATE()) -1 
  GROUP BY Mese 
 ) as tmp
RIGHT JOIN all_months on all_months.month_num=tmp.Mese
ORDER BY all_months.month_num ASC

您的查询现在已经完成tmp，之后正在订购。结果是：

month_num   Quantita
1           1
2           2
3           0
4           0
5           0
6           0
7           0
8           3
9           1
10          0
11          1
12          1

演示：这里

推荐阅读