asp教程+access按年,按月统计数据
/*
字段:d_id(自动编号), d_je(货币型),d_sxrq(日期型)
表数据如下:
d_idd_jed_sxrq
1202009-5-1
2302009-5-6
3102009-6-5
4152009-6-25
552009-8-8
6502009-9-9
7202010-5-1
8402010-5-6
9152010-6-5
10252010-6-25
11552010-8-8
12652010-9-9
想按年按月来统计:
如果选择2009年得到
月份 1 2 3 4 567891011 12
金额 0 0 0 0 50 25 0550 000
如果选择2010年得到:
月份 1 2 3 4 567891011 12
金额 0 0 0 0 60 40 05565000
*/
Select Year(D_SXRQ) As Y,
Sum(IIF(Month(D_SXRQ) = 1, D_JE, 0)) As M01,
Sum(IIF(Month(D_SXRQ) = 2, D_JE, 0)) As M02,
Sum(IIF(Month(D_SXRQ) = 3, D_JE, 0)) As M03,
Sum(IIF(Month(D_SXRQ) = 4, D_JE, 0)) As M04,
Sum(IIF(Month(D_SXRQ) = 5, D_JE, 0)) As M05,
Sum(IIF(Month(D_SXRQ) = 6, D_JE, 0)) As M06,
Sum(IIF(Month(D_SXRQ) = 7, D_JE, 0)) As M07,
Sum(IIF(Month(D_SXRQ) = 8, D_JE, 0)) As M08,
Sum(IIF(Month(D_SXRQ) = 9, D_JE, 0)) As M09,
Sum(IIF(Month(D_SXRQ) = 10, D_JE, 0)) As M10,
Sum(IIF(Month(D_SXRQ) = 11, D_JE, 0)) As M11,
Sum(IIF(Month(D_SXRQ) = 12, D_JE, 0)) As M12
FromD_FY
GroupBy Year(D_SXRQ);