使用MySQL动态创建日期期间 [英] Dynamically creating date periods using MySQL

问题描述

我尝试从3月1日至3日的日期获取葡萄数量。

您会注意到3月2日没有插入葡萄。

我可以显示3月1日，2日和3日的查询，但显示3月2日的0次计数

在上面的图片中只显示有葡萄的日期..

这里是mySQL查询

  SELECT`fruitDate`，`fruitName`，COUNT（*）
 FROM`tbl_fruits` 
 WHERE`fruitName` =Grapes
 GROUP BY`fruitDate 
  

UPDATE 2：

使用此查询：

  SELECT f.fruitDate，f.fruitName，f1.count FROM tbl_fruits f 
 LEFT JOIN（select fruitDate，COUNT（*）as count from tbl_fruits d WHERE d.fruitName ='Grapes'GROUP BY d.fruitDate）as f1 ON（f.fruitDate = f1.fruitDate）
 GROUP BY f.fruitDate 
  

得到这个结果...但它的dsplaying diffrent果...我的查询错误了？

解决方案

记住有一个动态创建不需要创建表的日期范围：

 选择aDate from（
 select @maxDate  -  interval （a.a +（10 * ba）+（100 * ca）+（1000 * da））day a从
开始的日期（选择0作为联合全选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）a，/ * 10天范围* / 
（选择0作为并集全选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）b，/ * 100天范围* / 
（选择0作为并集全选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）c，/ * 1000天范围* / 
（选择0作为并集全选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）d，/ * 10000 day range * / 
（select @minDate：='2001-01-01'，@maxDate：=' 2002-02-02'）e 
）f 
其中aDate介于@minDate和@maxDate之间
  

根据日期范围的长度，可以通过删除表（d，c，b和a）来减少动态生成结果的量（10000天表示超过27年的记录，每次表示一天）他们从上面的公式。设置 @minDate 和 @maxDate 变量将允许您指定要过滤结果之间的日期。 / p>

编辑：

我看到您仍在寻找解决方案。尝试：

 选择c.date，f.fruitName，count（f.fruitName ='Grapes'）
从tbl_calendar c 
 left join tbl_fruits f 
 on c.date = f.fruitDate和f.fruitName ='Grapes'
 group by c.date，f.fruitName 
  

如果您还想从创建的表中过滤额外的日期，请使用以下查询：

  select c.date，f.fruitName，count（f.fruitName ='Grapes'）
 from tbl_calendar c 
 left join tbl_fruits f 
 on c.date = f.fruitDate和f.fruitName ='Grapes'
组c.date，f.fruitName 
 c.date介于
之间（select min（fruitDate ）from tbl_fruits）and 
（select max（fruitDate）from tbl_fruits）
  




I trying to get the Grape count from dates March 1 - 3.

You will notice that on March 2 - there are no grapes inserted..

I'st possible to show a query from dates March 1, 2 and 3 but showing 0 count for March 2

In this image above only shows dates where there are grapes..

Here is mySQL query

SELECT  `fruitDate` ,  `fruitName` , COUNT( * ) 
FROM  `tbl_fruits` 
WHERE  `fruitName` =  "Grapes"
GROUP BY  `fruitDate

UPDATE 2:

Using this query:

SELECT f.fruitDate, f.fruitName, f1.count FROM tbl_fruits f
    LEFT JOIN (SELECT fruitDate, COUNT(*) as count from tbl_fruits d WHERE d.fruitName='Grapes' GROUP BY d.fruitDate) as f1 ON (f.fruitDate = f1.fruitDate) 
    GROUP BY f.fruitDate

I got this result..but its dsplaying diffrent fruit..something wrong with my query?

解决方案

Remember there is a dynamically (and a bit ugly) solution to creating a date range that does not require creating a table:

select aDate from (
  select @maxDate - interval (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) day aDate 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) a, /*10 day range*/
  (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) b, /*100 day range*/
  (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) c, /*1000 day range*/
  (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) d, /*10000 day range*/
  (select @minDate := '2001-01-01', @maxDate := '2002-02-02') e
) f
where aDate between @minDate and @maxDate

Depending on the length of the date range you can reduce the amount of dynamically generated results (10000 days means over 27 years of records each representing one day) by removing tables (d, c, b and a) and removing them from the upper formula. Setting the @minDate and @maxDate variables will allow you to specify the dates between you want to filter the results.

Edit:

I see you're still looking for a solution. Try this:

select c.date, f.fruitName, count(f.fruitName = 'Grapes')
from tbl_calendar c
left join tbl_fruits f
on c.date = f.fruitDate and f.fruitName = 'Grapes'
group by c.date, f.fruitName

If you also want to filter the extra dates from the created table, use this query:

select c.date, f.fruitName, count(f.fruitName = 'Grapes')
from tbl_calendar c
left join tbl_fruits f
on c.date = f.fruitDate and f.fruitName = 'Grapes'
group by c.date, f.fruitName
having c.date between
  (select min(fruitDate) from tbl_fruits) and
  (select max(fruitDate) from tbl_fruits)

这篇关于使用MySQL动态创建日期期间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！