2.8w+
社区成员
- 主页
根据日期相同求和
我这程序本身是根据年、月来查询数据的，当在相同一天有几个数据，我想要将同一天的数据进行求和我该怎么做？？
如下：
ID DATE Rate
1 2009-1-1 20
2 2009-1-1 10
3 2009-1-1 10
4 2009-1-2 15
5 2009-1-3 10
.....
搜索条件是 2009-1
需要显示的结果是
2009-1-1 40
2009-1-2 15
2009-1-3 10
如下：
ID DATE Rate
1 2009-1-1 20
2 2009-1-1 10
3 2009-1-1 10
4 2009-1-2 15
5 2009-1-3 10
.....
搜索条件是 2009-1
需要显示的结果是
2009-1-1 40
2009-1-2 15
2009-1-3 10
...全文
请发表友善的回复…
发表回复
lonan 2009-08-04
楼上说的不明不白，请问高手我该如何改上面的代码？？？？？
fumingk 2009-08-04
sql="select date as 名称,sum(rate) as rate FROM table group by date"
lonan 2009-08-04
可能上面各位还没理会我的意思， D_M为搜索月份 D_Y为搜索年份
DIM D_M,D_Y,RS,Sqlstr
SqlStr="Select * From shop where month(Date)="&D_M&" and year(Date)="&D_Y&" Order By Date
Set RS=Server.CreateObject("ADODB.RECORDSET")
RS.Open SqlStr,Conn,1,1
IF RS.Eof And RS.Bof Then
Response.Write "<tr><td><font color=""#ff0000"">暂无月数据 </font></td></tr>"
Else
Response.Write "<tr bgcolor=""#0099CC""> <td><font color=""#000"">日期</font></td> <td><font color=""#000"">每日总值</font></td> </tr>"
Do While Not RS.Eof
If Not Response.IsClientConnected Then Response.end
Response.Write" <tr bgcolor=""#FFFFFF""> <td>"&RS("Date")&" </td> <td height=""25"">"&rs("rate")&" </td> </tr>"
RS.MoveNext
Loop
End IF
如我搜索的条件是2009年1月
现在显示出来的值是
日期 每日总值
2009-1-1 20
2009-1-1 10
2009-1-1 10
2009-1-2 15
2009-1-3 10
而我要显示出来的结果是
日期 每日总值
2009-1-1 40
2009-1-2 15
2009-1-3 10
我需要将同一天有多个值进行求和显示求和总值
请教各位上面的代码我该怎么写！！！！！！！！急！！！！
DIM D_M,D_Y,RS,Sqlstr
SqlStr="Select * From shop where month(Date)="&D_M&" and year(Date)="&D_Y&" Order By Date
Set RS=Server.CreateObject("ADODB.RECORDSET")
RS.Open SqlStr,Conn,1,1
IF RS.Eof And RS.Bof Then
Response.Write "<tr><td><font color=""#ff0000"">暂无月数据 </font></td></tr>"
Else
Response.Write "<tr bgcolor=""#0099CC""> <td><font color=""#000"">日期</font></td> <td><font color=""#000"">每日总值</font></td> </tr>"
Do While Not RS.Eof
If Not Response.IsClientConnected Then Response.end
Response.Write" <tr bgcolor=""#FFFFFF""> <td>"&RS("Date")&" </td> <td height=""25"">"&rs("rate")&" </td> </tr>"
RS.MoveNext
Loop
End IF
如我搜索的条件是2009年1月
现在显示出来的值是
日期 每日总值
2009-1-1 20
2009-1-1 10
2009-1-1 10
2009-1-2 15
2009-1-3 10
而我要显示出来的结果是
日期 每日总值
2009-1-1 40
2009-1-2 15
2009-1-3 10
我需要将同一天有多个值进行求和显示求和总值
请教各位上面的代码我该怎么写！！！！！！！！急！！！！
lonan 2009-08-04
本人改好了，如下：
DIM D_M,D_Y,RS,d_d,Sqlstr,Srate,RS2
SqlStr="Select * From shop where id in(select Min(id) from shop Group By Date ) and month(Date)="&D_M&" and year(Date)="&D_Y&" Order By Date '同日期显示一个数据
Set RS=Server.CreateObject("ADODB.RECORDSET")
RS.Open SqlStr,Conn,1,1
IF RS.Eof And RS.Bof Then
Response.Write " <tr> <td> <font color=""#ff0000"">暂无月数据 </font> </td> </tr>"
Else
Response.Write " <tr bgcolor=""#0099CC""> <td> <font color=""#000"">日期 </font> </td> <td> <font color=""#000"">每日总值 </font> </td> </tr>" 、
Do While Not RS.Eof
If Not Response.IsClientConnected Then Response.end
d_d=RS("Date") '获得循环日期
SqlStr="Select Sum(rate) as Srate From shop where day(Date)='"&day(d_d)&"' and month(Date)="&D_M&" and year(Date)="&D_Y&" "
Set RS2=Server.CreateObject("ADODB.RECORDSET")
RS2.Open SqlStr,Conn,1,1 '计算相同日期RATE之和
Response.Write" <tr bgcolor=""#FFFFFF""> <td>"&RS("Date")&" </td> <td height=""25"">"&rs2("Srate")&" </td> </tr>"
RS.MoveNext
Loop
End IF
DIM D_M,D_Y,RS,d_d,Sqlstr,Srate,RS2
SqlStr="Select * From shop where id in(select Min(id) from shop Group By Date ) and month(Date)="&D_M&" and year(Date)="&D_Y&" Order By Date '同日期显示一个数据
Set RS=Server.CreateObject("ADODB.RECORDSET")
RS.Open SqlStr,Conn,1,1
IF RS.Eof And RS.Bof Then
Response.Write " <tr> <td> <font color=""#ff0000"">暂无月数据 </font> </td> </tr>"
Else
Response.Write " <tr bgcolor=""#0099CC""> <td> <font color=""#000"">日期 </font> </td> <td> <font color=""#000"">每日总值 </font> </td> </tr>" 、
Do While Not RS.Eof
If Not Response.IsClientConnected Then Response.end
d_d=RS("Date") '获得循环日期
SqlStr="Select Sum(rate) as Srate From shop where day(Date)='"&day(d_d)&"' and month(Date)="&D_M&" and year(Date)="&D_Y&" "
Set RS2=Server.CreateObject("ADODB.RECORDSET")
RS2.Open SqlStr,Conn,1,1 '计算相同日期RATE之和
Response.Write" <tr bgcolor=""#FFFFFF""> <td>"&RS("Date")&" </td> <td height=""25"">"&rs2("Srate")&" </td> </tr>"
RS.MoveNext
Loop
End IF
lzp4881 2009-08-03
使用分组查询
SELECT [date],sum(rate) as rate FROM table group by [date]
SELECT [date],sum(rate) as rate FROM table group by [date]
lingfeng179 2009-08-03
你可以采用循环的方式在里面使用
d1=DateDiff("d",日期1，日期2)
返回值为0的话两个日期相等 然后再进行相加就可以了
d1=DateDiff("d",日期1，日期2)
返回值为0的话两个日期相等 然后再进行相加就可以了
liuwei_IT_love 2009-08-03
<%str="select * from table where date order by date desc"
do while not rs.eof
rate=rate+rate
%>
<%=rs("rate")%>
<%rs.movenext
loop
%>
do while not rs.eof
rate=rate+rate
%>
<%=rs("rate")%>
<%rs.movenext
loop
%>
phyllis2001 2009-08-03
SELECT date,sum(rate) FROM tableName group by date