我有开始日期和结束日期的记录,如下所示:

id  start_date   end_date
1   2016-01-01   2016-10-31
2   2016-06-01   2016-12-31
3   2016-06-01   2016-07-31


我必须知道每月活动的记录数(或者更好的方法是:在给定期间内所有月份的第一天)。在为2016计算时,计数看起来像这样:

jan: 1
feb: 1
mar: 1
apr: 1
may: 1
jun: 3
jul: 3
aug: 2
sep: 2
oct: 2
nov: 1
dec: 1


我想到的解决方案是创建一个TEMP TABLE,其中包含给定期间的所有适用日期:

date
2016-01-01
2016-02-01
...


这使查询非常容易:

SELECT
  COUNT(*),
  m.date
FROM
  months m
INNER JOIN table t
  ON m.date BETWEEN t.start_date AND t.end_date
GROUP BY
  m.date


这将产生我正在寻找的结果。然而;我确实觉得这可以更轻松地完成。我就是不知道

有什么建议么?

最佳答案

即使看起来很丑,也可以通过以下方式进行操作:

假设您要运行报告,并且只对“某年的月份”感兴趣,则可以执行以下查询:

select m,Count(id) FROM (
SELECT 1 as m UNION 
SELECT 2 as m UNION 
SELECT 3 as m UNION 
SELECT 4 as m UNION 
SELECT 5 as m UNION 
SELECT 6 as m UNION 
SELECT 7 as m UNION 
SELECT 8 as m UNION 
SELECT 9 as m UNION 
SELECT 10 as m UNION 
SELECT 11 as m UNION 
SELECT 12 as m) AS tabseq
CROSS JOIN x WHERE 
  (year (start_date) = 2016 AND year (end_date) = 2016 AND m >= month(start_date) AND m <= month(end_date)) -- starts abd ends this year
  or
  (year (start_date) < 2016 AND year (end_date) = 2016 AND m <= month(end_date)) -- ends this year, consider months until end of contract
  or
  (year (start_date) < 2016 AND year (end_date) > 2016) -- spans the year, ignore month,
  or
  (year (start_date) = 2016 AND year (end_date) > 2016 AND m >= month(start_date)) -- starts this year, consider months until end of year
GROUP BY m;


结果:

m   count(id)
1   1
2   1
3   1
4   1
5   1
6   3
7   3
8   2
9   2
10  2
11  1
12  1

08-04 13:43