在 SQL 中按月生成年初至今报告
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7418676/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generate year to date by month report in SQL
提问by Swoop
Possible Duplicate:
Running total by grouped records in table
可能的重复：
按表中的分组记录运行总计
I am trying to put together an SQL statement that returns the SUM of a value by month, but on a year to date basis. In other words, for the month of March, I am looking to get the sum of a value for the months of January, February, and March.
我正在尝试组合一个 SQL 语句，该语句按月返回值的 SUM，但以年初至今为基础。换句话说，对于 3 月份，我希望得到 1、2 和 3 月份的值的总和。
I can easily do a group by to get a total for each month by itself, and potentially calculate the year to date value I need in my application from this data by looping through the results set. However, I was hoping to have some of this work handled with my SQL statement.
我可以轻松地进行分组以单独获得每个月的总数，并可能通过循环遍历结果集，根据这些数据计算我的应用程序中所需的年初至今值。但是，我希望通过我的 SQL 语句来处理其中的一些工作。
Has anyone ever tackled this type of problem with an SQL statement, and if so, what is the trick that I am missing?
有没有人用 SQL 语句解决过这种类型的问题，如果是，我缺少什么技巧？
My current sql statement for monthly data is similar to the following:
我当前的月度数据sql语句类似如下：
Select month, year, sum(value) from mytable group by month, year
If I include a where clause on the month, and only group by the year, I can get the result for a single month that I am looking for:
如果我在月份中包含 where 子句，并且仅按年份分组，则可以获得我正在寻找的单个月份的结果：
select year, sum(value) from mytable where month <= selectedMonth group by year
However, this requires me to have a particular month pre-selected or to utilize 12 different SQL statements to generate one clean result set.
但是，这要求我预先选择特定月份或使用 12 条不同的 SQL 语句来生成一个干净的结果集。
Any guidance that can be provided would be greatly appreciated!
任何可以提供的指导将不胜感激！
Update: The data is stored on an IBM iSeries.
更新：数据存储在 IBM iSeries 上。
采纳答案by Icarus
declare @Q as table
(
mmonth INT,
value int
)
insert into @Q
values
(1,10),
(1,12),
(2,45),
(3,23)
select sum(January) as UpToJanuary,
sum(February)as UpToFebruary,
sum(March) as UpToMarch from (
select
case when mmonth<=1 then sum(value) end as [January] ,
case when mmonth<=2 then sum(value) end as [February],
case when mmonth<=3 then sum(value) end as [March]
from @Q
group by mmonth
) t
Produces:
产生：
UpToJanuary UpToFebruary UpToMarch
22 67 90
You get the idea, right?
你明白了，对吧？
NOTE: This could be done easier with PIVOT tables but I don't know if you are using SQL Server or not.
注意：使用 PIVOT 表可以更轻松地完成此操作，但我不知道您是否使用 SQL Server。
回答by mishau
create table mon
(
[y] int not null,
[m] int not null,
[value] int not null,
primary key (y,m))
select a.y, a.m, a.value, sum(b.value)
from mon a, mon b
where a.y = b.y and a.m >= b.m
group by a.y, a.m, a.value
2011 1 120 120
2011 2 130 250
2011 3 500 750
2011 4 10 760
2011 5 140 900
2011 6 100 1000
2011 7 110 1110
2011 8 90 1200
2011 9 70 1270
2011 10 150 1420
2011 11 170 1590
2011 12 600 2190
回答by a_horse_with_no_name
As far as I know DB2 does support windowing functions although I don't know if this is also supported on the iSeries version.
据我所知，DB2 确实支持窗口功能，尽管我不知道 iSeries 版本是否也支持此功能。
If windowing functions are supported (I believe IBM calls them OLAP functions) then the following should return what you want (provided I understood your question correctly)
如果支持窗口函数（我相信 IBM 称它们为 OLAP 函数），则以下内容应返回您想要的内容（前提是我正确理解了您的问题）
select month,
year,
value,
sum(value) over (partition by year order by month asc) as sum_to_date
from mytable
order by year, month
回答by pupssman
You should try to join the table to itself by month-behind-a-month condition and generate a synthetic month-group code to group by as follows:
您应该尝试按月后一个月条件将表连接到自身，并生成合成的月组代码以进行分组，如下所示：
select
sum(value),
year,
up_to_month
from (
select a.value,
a.year,
b.month as up_to_month
from table as a join table as b on a.year = b.year and b.month => a.month
)
group by up_to_month, year
gives that:
给出：
db2 => select * from my.rep
db2 => 从 my.rep 中选择 *
VALUE YEAR MONTH
----------- ----------- -----------
100 2011 1
200 2011 2
300 2011 3
400 2011 4
db2 -t -f rep.sql
db2 -t -f 代表.sql
1 YEAR UP_TO_MONTH
----------- ----------- -----------
100 2011 1
300 2011 2
600 2011 3
1000 2011 4
