gpt4 book ai didi

mysql - 日期 : group by month from day 2 to day 1 next month

转载 作者:行者123 更新时间:2023-11-29 14:31:15 28 4
gpt4 key购买 nike

我有这样的查询:

SELECT EXTRACT(MONTH FROM d.mydate) AS synmonth, SUM(apcp) AS apcptot
FROM t_synop_data2 d
WHERE d.mydate
BETWEEN '2011-01-01' AND '2011-12-31'
AND d.idx_synop = '06712'
GROUP BY synmonth

此查询添加了一个月内的所有降雨 (apcp),如下所示:

1   32.8  => from 2011.01.01 to 2011.01.31
2 27.2 => from 2011.02.01 to 2011.02.28
3 21.0
4 21.8
5 88.5
6 131.4
7 118.6
8 57.1
9 80.9
10 84.6
11 1.1
12 143.5 => from 2011.12.01 to 2011.12.31

这就是我想要的,但有一点不同。

这个区别是我必须从该月的第 2 天到下个月的第 1 天添加 apcp,然后返回如上所述的结果。

1   132.8 => from 2011.01.02 to 2011.02.01
2 27.2 => from 2011.02.02 to 2011.03.01
3 21.0
4 21.8
5 88.5
6 131.4
7 118.6
8 57.1
9 80.9
10 84.6
11 1.1
12 143.5 => from 2011.12.02 to 2012.01.01

我尝试了 add_date()、extract() 或 date_format() 但没有结果。

谢谢你的回答文斯

最佳答案

这是查询:

SELECT EXTRACT(MONTH FROM ADDDATE(d.mydate,-1) ) AS synmonth
, SUM(apcp) AS apcptot
FROM t_synop_data2 AS d
WHERE ADDDATE(d.mydate,-1) BETWEEN '2011-01-01' AND '2012-12-31'
AND d.idx_synop = '06712'
GROUP BY synmonth

您可以通过添加两列来检查结果,如下所示:

SELECT EXTRACT(MONTH FROM ADDDATE(d.mydate,-1) ) AS synmonth
, SUM(apcp) AS apcptot
, MIN(d.mydate) AS date_min
, MAX(d.mydate) AS date_max
FROM t_synop_data2 AS d
WHERE ADDDATE(d.mydate,-1) BETWEEN '2011-01-01' AND '2012-12-31'
AND d.idx_synop = '06712'
GROUP BY synmonth

关于mysql - 日期 : group by month from day 2 to day 1 next month,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10001156/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com