情况:我需要日期之间的自定义间隔。当我尝试GROUP BY年份时遇到的问题以及我得到的结果总计到给定年份。我需要自定义每年从12月20日开始的时间间隔:上年00:00:00至12月19日用款时间:23:59:59,这是我的一些数据:
表格-历史:
id | date | income | spent
--------------------------------------------
1 | 2019-12-21 17:15:00 | 600,00 | NULL
2 | 2019-12-23 12:55:00 | 183,00 | NULL
3 | 2019-12-30 20:05:00 | NULL | 25,00
4 | 2020-01-01 15:35:00 | NULL | 13,00
5 | 2020-01-01 20:25:00 | NULL | 500,50
6 | 2020-12-10 10:25:00 | NULL | 5,50
7 | 2021-05-22 12:45:00 | 1098,00 | NULL
8 | 2021-05-23 10:18:00 | NULL | 186,00
9 | 2021-11-25 12:32:00 | NULL | 10,00
10 | 2021-12-23 10:35:00 | NULL | 10,00
预期结果:
Year | Summary Income | Summary Spent | Difference
--------------------------------------------------
2020 | 783,00 | 544,00 | 239,50
2021 | 1098,00 | 196,00 | 902,00
2022 | 0,00 | 10,00 | -10,00
我已经设法在一个过程中的循环的帮助下得到了一个结果:
...
SET @Aa = (SELECT MIN(date) FROM History);
CREATE TEMPORARY TABLE Yr (Year VARCHAR(4), Income FLOAT(8,2), Spent FLOAT(8,2), differ FLOAT(8,2));
Yearly: LOOP
SET @Aa = @Aa + 1;
SET @From = CONCAT((@Aa - 1), '-12-20 00:00:00');
SET @To = CONCAT(@Aa, '-12-19 23:59:59');
SET @Count = (SELECT SUM(income) FROM History WHERE date >= @From AND date <= @To);
SET @diff = (SELECT SUM(spent) FROM History WHERE date >= @From AND date <= @To);
INSERT INTO Yr (Year, Income, Spent, differ) VALUES (@Aa, @Count, @diff, (@Count - @diff));
IF (@Aa = (SELECT MAX(YEAR(date)) FROM History)) THEN LEAVE Yearly; END IF;
END LOOP;
SELECT * FROM Yr;
...
**问题:**我想知道是否可以使用精简的SQL查询而不使用循环来获得年度摘要的自定义间隔?
1条答案
按热度按时间rslzwgfq1#
您可以在应用year函数之前简单地在日期上加上11天来获得此分组,例如: