asp+access按年，按月统计，求助。

[Derekwong]

表d_fy

字段：d_id(自动编号)， d_je（货币型）,d_sxrq（日期型）

表数据如下：

d_id d_je d_sxrq
1 20 2009-5-1
2 30 2009-5-6
3 10 2009-6-5
4 15 2009-6-25
5 5 2009-8-8
6 50 2009-9-9
7 20 2010-5-1
8 40 2010-5-6
9 15 2010-6-5
10 25 2010-6-25
11 55 2010-8-8
12 65 2010-9-9

想按年按月来统计：
如果选择2009年得到
月份 1 2 3 4 5 6 7 8 9 10 11 12
金额 0 0 0 0 50 25 0 5 50 0 0 0

如果选择2010年得到：
月份 1 2 3 4 5 6 7 8 9 10 11 12
金额 0 0 0 0 60 40 0 55 65 0 0 0

请问这样的分组查询应该怎么写，前台怎么运用，谢谢。

[挨踢直男]

select sum(case month(d_sxrq) when 1 then d_je else 0 end) as [1月],如上,,,,, from d_fy where year(d_sxrq) = 2009

[xshf12345]

应该可以从数据库那着手，查出来的就是某年某月的统计量
select count(month(d_sxrq)) from order by year(d_sxrq)....大致思路是这样，没测试过

[Derekwong]

谢谢两位回复，

aspwebchh，我用你给你的测试了一下，取不到数据。
<%
set rs=server.CreateObject("adodb.recordset")
rs.open "select sum(case month(d_sxrq) when 1 then d_je else 0 end) as 1y,sum(case month(d_sxrq) when 2 then d_je else 0 end) as 2y,sum(case month(d_sxrq) when 3 then d_je else 0 end) as 3y,sum(case month(d_sxrq) when 4 then d_je else 0 end) as 4y,sum(case month(d_sxrq) when 5 then d_je else 0 end) as 5y,sum(case month(d_sxrq) when 6 then d_je else 0 end) as 6y,sum(case month(d_sxrq) when 7 then d_je else 0 end) as 7y,sum(case month(d_sxrq) when 8 then d_je else 0 end) as 8y,sum(case month(d_sxrq) when 9 then d_je else 0 end) as 9y,sum(case month(d_sxrq) when 10 then d_je else 0 end) as 10y,sum(case month(d_sxrq) when 11 then d_je else 0 end) as 11y,sum(case month(d_sxrq) when 12 then d_je else 0 end) as 12y from d_fy where year(d_sxrq) = 2010 ",conn,1,1
%>

下面直接用<%rs("1y")%><%rs("2y")%>.........

显示是空的，还请麻烦你看看

[Derekwong]

<%=rs("1y")%><%=rs("2y")%>.........

[xshf12345]

sql语句在查询分析器中执行是什么样的

[Derekwong]

语法错误，操作符丢失

[wj_86]

其实楼主可以再加3个字段，年，月，日三个单独字段

[shan1119]

Table:Mon
  m
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

<%
    dim db, rs, sql, i
    dim arr(12)
    Set db=Server.CreateObject("ADODB.Connection")
    db.Provider = "Microsoft.Jet.OLEDB.4.0"
    db.ConnectionString = Server.MapPath("test.mdb")
    
    db.open
    
    sql="select Mon.m, sum(iif(year(d_fy.d_sxrq)='2010',d_fy.d_je,0)) as s"
    sql=sql & "  from  Mon left join d_fy on Mon.m=month(d_fy.d_sxrq)"
    sql=sql & " group by Mon.m"
    
    Set rs = db.Execute(sql)
    while not rs.eof
        'response.write (rs("m") & "" & rs("s") & "<br>")
        arr(rs("m"))=rs("s")
        rs.movenext
    wend
    set rs=nothing
    
    for i=1 to 12
        response.write(i & vbcrlf)
    next
        response.write("<br>")
    for i=1 to 12
        response.write(arr(i) & vbcrlf)
    next
    
    db.close
    Set db = Nothing
    
%>