如何使用间隔中的年和月动态连续显示零? [英] How can you show zero in a row using year and month dynamic in an interval?
问题描述
我正在尝试创建一个查询，以显示以 SET YEAR和MONTH 为间隔的12个月(年份和月份为动态值-间隔为12个月的静态值)时的计数).
I'm trying to create a query to show count when SET YEAR and MONTH at an interval of 12 months (the year and month are dynamic values - the interval is a static value of 12 months).
例如:设置月份= 12，年份= 2013
This will count from 2013-12-01 to 2014-11-30
其他示例:设置月份= 1和年份= 2014
This will count from 2014-01-01 to 2014-12-31
我创建了此演示，但还想在不计算行数时显示0在表格中，我在这里使用了 COALESCE :
I created this demo but also want to show 0 when the rows's count are not in the table, here I used COALESCE:
SET @var_year = '2013';
SET @var_month = '12';
SET @from = STR_TO_DATE(CONCAT(@var_year, '/', @var_month, '/01'), '%Y/%m/%d');
SET @to = DATE_ADD(@from, INTERVAL 12 MONTH);
SELECT COALESCE(count(created_at), 0) AS count_all,
year(created_at) as actual_year,
month(created_at)as actual_month
FROM creations
WHERE created_at BETWEEN @from AND @to
GROUP BY month(created_at),year(created_at)
ORDER BY year(created_at),month(created_at) ASC
但不幸的是得到了这个结果:
But unfortunately got this result:
count_all actual_year actual_month
1 2013 12
3 2014 1
2 2014 2
1 2014 3
1 2014 4
1 2014 5
1 2014 6
2 2014 8
1 2014 9
1 2014 10
1 2014 11
我希望得到这样的结果:
I expect this result:
count_all actual_year actual_month
1 2013 12
3 2014 1
2 2014 2
1 2014 3
1 2014 4
1 2014 5
1 2014 6
0 2014 7 <------ HERE I WANT 0 WHEN IS NOT IN DB
2 2014 8
1 2014 9
1 2014 10
1 2014 11
我使用month = 12 and year = 2013 创建了这个实时演示，工作正常，但是在测试并更改了月份或年份的值之后，计数变差，因此该查询无法正常运行，例如，该
I created this live demo using month = 12 and year = 2013, it actually works, but after testing and changing month or year values, got bad counts, so the query is not working correctly for example this demo using month= 1 and year =2013 isn't working correctly
SET @var_year = '2014';
SET @var_month = '1';
SET @from = STR_TO_DATE(CONCAT(@var_year, '/', @var_month, '/01'), '%Y/%m/%d');
SET @to = DATE_ADD(@from, INTERVAL 12 MONTH);
SELECT IFNULL(counter,0) as counter, literals.y AS actual_year, literals.m AS actual_month
FROM (
SELECT @var_month AS m, @var_year + 0 AS y
UNION SELECT IF((@var_month + 1) % 12, (@var_month + 1) % 12, 12) AS m, @var_year + ((@var_month + 1) DIV 12) AS y
UNION SELECT IF((@var_month + 2) % 12, (@var_month + 2) % 12, 12) AS m, @var_year + ((@var_month + 2) DIV 12) AS y
UNION SELECT IF((@var_month + 3) % 12, (@var_month + 3) % 12, 12) AS m, @var_year + ((@var_month + 3) DIV 12) AS y
UNION SELECT IF((@var_month + 4) % 12, (@var_month + 4) % 12, 12) AS m, @var_year + ((@var_month + 4) DIV 12) AS y
UNION SELECT IF((@var_month + 5) % 12, (@var_month + 5) % 12, 12) AS m, @var_year + ((@var_month + 5) DIV 12) AS y
UNION SELECT IF((@var_month + 6) % 12, (@var_month + 6) % 12, 12) AS m, @var_year + ((@var_month + 6) DIV 12) AS y
UNION SELECT IF((@var_month + 7) % 12, (@var_month + 7) % 12, 12) AS m, @var_year + ((@var_month + 7) DIV 12) AS y
UNION SELECT IF((@var_month + 8) % 12, (@var_month + 8) % 12, 12) AS m, @var_year + ((@var_month + 8) DIV 12) AS y
UNION SELECT IF((@var_month + 9) % 12, (@var_month + 9) % 12, 12) AS m, @var_year + ((@var_month + 9) DIV 12) AS y
UNION SELECT IF((@var_month +10) % 12, (@var_month +10) % 12, 12) AS m, @var_year + ((@var_month +10) DIV 12) AS y
UNION SELECT IF((@var_month +11) % 12, (@var_month +11) % 12, 12) AS m, @var_year + ((@var_month +11) DIV 12) AS y)
AS literals
LEFT JOIN
(SELECT count(*) as counter, year(created_at) y, month(created_at) as m
FROM creations WHERE created_at BETWEEN @from AND @to GROUP BY month(created_at),year(created_at)
ORDER BY year(created_at),month(created_at) ASC
) AS counts
ON literals.m = counts.m AND literals.y = counts.y;
我花了几个月的时间搜索有关如何解决此问题的信息.
I spent months searching information about how to deal with this problem.
如果有人可以帮助我，是否有可能解决或其他技巧或疑问?
If somebody can help me, is it impossible to resolve or another trick or query?
推荐答案
妙趣横生:)
您可以生成一个表，其中包含12个月间隔所需的年和月，然后对其进行外部联接:
You can generate a table that will hold Year and Month needed for the 12 months interval and then, do an outer join on it :
SET @var_year = '2013'
SET @var_month = '12'
SELECT @row := @row + (case when right(@row,2) = "12" then 89 else 1 end) as YearMonth
FROM
(select 0 union all select 1 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11) t,
(SELECT @row:=CONCAT(@var_year,right(concat('0',(@var_month-1)),2))) r
会给予:
| YearMonth |
|-----------|
| 201312 |
| 201401 |
| 201402 |
| 201403 |
| 201404 |
| 201405 |
| 201406 |
| 201407 |
| 201408 |
| 201409 |
| 201410 |
| 201411 |
因此，请尝试以下操作(例如，以2014为年份，以1为月份):
So try this (example with 2014 as year and 1 as month) :
MySQL 5.5架构设置:
CREATE TABLE creations(`id` int, `created_at` date )
;
INSERT INTO creations
(`id`, `created_at`)
VALUES
(1, '2013-12-11'),
(2, '2014-01-11'),
(3, '2014-01-21'),
(4, '2014-01-12'),
(5, '2014-02-22'),
(6, '2014-02-13'),
(7, '2014-03-12'),
(8, '2014-04-23'),
(9, '2014-05-23'),
(10,'2014-06-23'),
(11,'2014-08-23'),
(12,'2014-08-23'),
(13,'2014-09-23'),
(14,'2014-10-23'),
(15,'2014-11-23'),
(16,'2014-12-23')
;
**查询**:
SET @var_year = '2014'
SET @var_month = '1'
SELECT count(created_at) AS count_all,
left(YearMonth,4) as actual_year,
right(YearMonth,2) as actual_month
FROM (
SELECT @row := @row + (case when right(@row,2) = "12" then 89 else 1 end) as YearMonth FROM
(select 0 union all select 1 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11) t,
(SELECT @row:=CONCAT(@var_year,right(concat('0',(@var_month-1)),2))) r
) as YearMonthTable
LEFT OUTER JOIN creations ON
CONCAT(year(created_at),right(concat('0',month(created_at)),2)) = YearMonth
GROUP BY YearMonth
ORDER BY YearMonth ASC
结果 :
Results:
| count_all | actual_year | actual_month |
|-----------|-------------|--------------|
| 3 | 2014 | 01 |
| 2 | 2014 | 02 |
| 1 | 2014 | 03 |
| 1 | 2014 | 04 |
| 1 | 2014 | 05 |
| 1 | 2014 | 06 |
| 0 | 2014 | 07 |
| 2 | 2014 | 08 |
| 1 | 2014 | 09 |
| 1 | 2014 | 10 |
| 1 | 2014 | 11 |
| 1 | 2014 | 12 |
已
您也可以创建表，而不是每次都在子查询中生成表:
You can also create the table instead of generating it in a subquery every time :
MySQL 5.5架构设置:
CREATE TABLE YearMonthTable(`tblYear` int, `tblMonth` int)
;
INSERT INTO YearMonthTable
(`tblYear`,`tblMonth`)
VALUES
(2013,12),
(2014,1),
(2014,2),
(2014,3),
(2014,4),
(2014,5),
(2014,6),
(2014,7),
(2014,8),
(2014,9),
(2014,10),
(2014,11),
(2014,12),
(2015,1),
(2015,2),
(2015,3),
(2015,4),
(2015,5)
;
查询:
SET @var_year = '2014'
SET @var_month = '1'
SET @from = STR_TO_DATE(CONCAT(@var_year, '/', @var_month, '/01'), '%Y/%m/%d')
SET @to = DATE_ADD(DATE_ADD(@from, INTERVAL 12 MONTH), INTERVAL -1 DAY)
SELECT count(created_at) AS count_all,
tblYear as actual_year,
tblMonth as actual_month
FROM YearMonthTable
LEFT OUTER JOIN creations ON year(created_at) = tblYear AND
month(created_at) = tblMonth
WHERE STR_TO_DATE(CONCAT(tblYear, '/', tblMonth, '/01'), '%Y/%m/%d')
BETWEEN @from AND @to
GROUP BY tblMonth, tblYear
ORDER BY tblYear, tblMonth
结果 :
Results:
| count_all | tblYear | tblMonth |
|-----------|---------|----------|
| 3 | 2014 | 1 |
| 2 | 2014 | 2 |
| 1 | 2014 | 3 |
| 1 | 2014 | 4 |
| 1 | 2014 | 5 |
| 1 | 2014 | 6 |
| 0 | 2014 | 7 |
| 2 | 2014 | 8 |
| 1 | 2014 | 9 |
| 1 | 2014 | 10 |
| 1 | 2014 | 11 |
| 1 | 2014 | 12 |
这篇关于如何使用间隔中的年和月动态连续显示零?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
