问题描述
我有一个查询，它获取两个日期范围之间的数据.
I have a query that gets the data between two date ranges.
SELECT
MONTH(ENC_DT) as Month,
monthname(ENC.ENC_DT) as Month_Name,
year(ENC.ENC_DT) as Year,
ENC.ENC_DT,
ENC.ENC_ID
FROM
IDR_ENC ENC
where
DATE_FORMAT(ENC.ENC_DT, '%Y-%m-%d') >= '2014-01-01' and
DATE_FORMAT(ENC.ENC_DT, '%Y-%m-%d') <= '2014-10-16'
GROUP BY Month_Name, Year
对于在指定日期范围内缺少的月份，我需要它返回零.
I need it to return zeros for those months that are missing in between specified date range.
这可行吗?我检查了我能找到的每个网站，但是没有什么可以帮助我的.
Is this doable?I checked every website I could find but nothing that could help me.
推荐答案
我假设您要在结果集中查询与表中没有数据的月份相对应的行.
I assume you are asking for rows in your result set corresponding to the months in which you have no data in your table.
首先，相对而言，您正在将MySQL扩展滥用到 GROUP BY .请阅读这个. http://dev.mysql.com/doc/refman/5.6/zh-CN/group-by-extensions.html 对于我们和服务器来说，当您滥用该扩展名时，都很难猜测出您想要的是什么.
First of all, with respect, you are misusing a MySQL extension to GROUP BY. Please read this. http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html It's hard for us, and for the server, to guess what you want when you misuse that extension.
第三，您不需要 WHERE 子句中的 DATE_FORMAT()内容.日期可以直接比较.
Third, you don't need the DATE_FORMAT() stuff in your WHERE clause. Dates can be compared directly.
第三，您需要一个包含从1到12的所有数字的表.这很容易在简单的查询中生成，如下所示.稍后我们将使用它.
Third, you need a table of all the numbers from 1 to 12. This is easy to generate in a simple query, as follows. We'll use that later on.
SELECT 1 AS seq 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 UNION SELECT 12
第四，您需要将数字列表加入到另一个表中.
Fourth, you need to join your list of numbers to your other table.
SELECT seq.seq as Month,
MONTHNAME(CONCAT( YEAR(NOW()), '-', seq.seq, '-01')) as Month_Name,
year(ENC_DT) as Year
COUNT(*) as ENC_Record_Count
FROM (
SELECT 1 AS seq 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 UNION SELECT 12
) AS seq
LEFT JOIN IDR_ENC AS ENC ON seq.seq = MONTH(ENC.ENC_DT)
where ENC.ENC_DT >= '2014-01-01'
and ENC.ENC_DT <= '2014-10-16'
group by year(ENC.ENC_DT),seq.seq
order by year(ENC.ENC_DT),seq.seq
只要您只选择单个日历年内的日期，这将起作用-我们只有十二个月的整数.
This will work as long as you only select dates lying within a single calendar year -- we only have twelve months' worth of integers.
从整数中获取月份名称有些棘手的事情.
There's some tricky stuff to get the month name from the integer.
有关这种查询的更详细的讨论，请参见此. http://www.plumislandmedia.net/mysql/filling-缺少数据序列基数整数/
For a more elaborate discussion of this kind of query, please see this. http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/
这篇关于mysql查询中缺少月份的substitude零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！