MySQL-填写缺少的月份的行 [英] Mysql - filling rows for missing months

问题描述

天哪，它必须是如此简单，但我正在努力解决填写丢失的数据"问题.

Gosh, it must be so simple but I'm struggling with this 'filling out missing data' issue.

我有一个表，该表包含以下带有一些插入数据的列.

I have a table that has the following columns with some inserted data.

TABLE

year  month  payment
2014    3      100
2014    5      800
2014    9      200

我希望从此表中获得一个完整的月份，其2014年的付款价值.

And what I want from this table is to have a full range of months with its payment value from 2014.

Month    Payment
  1        0
  2        0
  3       100
  4        0
  5       800

  ...

  12       0

我尝试在SELECT中使用IFNULL，但失败了，非常糟糕...而且stackoverflow的搜索结果通常会结合两个或多个表来处理信息.解决这个问题的最快，最好的解决方案是什么?

I tried using IFNULL in select but failed so bad... and search results from stackoverflow usually join two or more tables to manipulate information. What would be the fastest and best solution to solve this problem?

推荐答案

对于缺少的月份，您可以使用所有月份的并集查询并与表联接

For missing months you can have a union query with all months and join with your table

SELECT 
t1.`year`,
t.`month`,
coalesce(t1.payment,0) payment
FROM
(SELECT 1 AS `month`
UNION 
SELECT 2 AS `month`
UNION 
....
SELECT 12 AS `month`
) AS t
LEFT JOIN your_table t1 on(t.`month` = t1.`month`)
WHERE ....

这篇关于MySQL-填写缺少的月份的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！