求助 SUM 和ROUP BY 的使用方法。感激不尽~！！！[

来源：互联网发布：北京软件开发公司编辑：程序博客网时间：2024/04/30 10:34

数据库如下：

chuhuo（表1，记录出货记录）

chuhuodan(出货单)，company（公司名称），chuhuodate（出货日期），zonger（出货总额）

chuhuodan（表2，主要用来记录收款情况）

chuhuodan（chuhuodan），shoukuanjiner（收款金额），shoukuandate（收款时间），shoukuanren（收款人），wancheng（是否完成收款。）

核心SQL语句该怎么写？

网页代码如下：

<%
chuhuodate=request("chuhuodate")
chuhuodan=request("chuhuodan")
company=request("company")
sheng=request("sheng")
shi=request("shi")
wancheng=request("wancheng")

%>
<body text="#000000">

%>
<form name="form1" method="post" action="">
<table width="1024" border="1" cellpadding="0" cellspacing="0" align="center" class=TableBorder>
    <tr height="22" valign="middle" align="center">
      <th width="14%">出货日期</th>
      <th width="14%">出货单</th>
      <th width="14%">客户名称</th>
      <th width="15%">省份</th>
      <th width="15%">城市</th>
      <th width="28%">完成</th>
      <th width="28%"> </th>
    </tr>
    <tr height="22" valign="middle" align="center">
      <td class=forumrow><label>
        <input name="chuhuodate" type="text" id="chuhuodate" value="<%=chuhuodate%>">
      </label></td>
      <td class=forumrow><input name="chuhuodan" type="text" id="chuhuodan" value="<%=chuhuodan%>"></td>
      <td class=forumrow><input name="company" type="text" id="company" value="<%=company%>"></td>
      <td class=forumrow><input name="sheng" type="text" id="sheng" value="<%=sheng%>"></td>
      <td class=forumrow><input name="shi" type="text" id="shi" value="<%=shi%>"></td>
      <td class=forumrow><label>
        <select name="wancheng">
          <option <%if wancheng="" then%>selected <%end if%>>全部</option>
          <option value="1"<% if wancheng="1" then%>selected<%end if%>>完成</option>
          <option value="0"<%if wancheng="0" then%>selected<%end if%>>未完成</option>
        </select>
      </label></td>
      <td class=forumrow><input type="submit" name="Submit" value="查找"></td>
    </tr>
</table>
</form>

<%'开始分页
    Const MaxPerPage=10000
       dim totalPut
       dim CurrentPage
       dim TotalPages
       dim j
       dim sql
        if Not isempty(request("page")) then
          currentPage=Cint(request("page"))
       else
          currentPage=1
       end if


    set rs=server.CreateObject("adodb.recordset")

   set rs=server.CreateObject("adodb.recordset")

sql= "select chuhuo.chuhuodan,chuhuo.zonger,sum(chuhuodan.shoukuan) from (select chuhuodan,sum(chuhuo.zonger) zonger from chuhuo group by chuhuodan)chuhuo left join chuhuodan on chuhuo.chuhuodan=chuhuodan.chuhuodan group by chuhuo.chuhuodan,chuhuo.zonger"


            rs.open sql,conn,1,1


     if err.number<>0 then
    response.write "数据库中无数据"
    end if

    if rs.eof And rs.bof then
           Response.Write "<p align='center' class='contents'> 对不起,没有找到相关信息！</p>"
       else
       totalPut=rs.recordcount

          if currentpage<1 then
              currentpage=1
          end if

          if (currentpage-1)*MaxPerPage>totalput then
         if (totalPut mod MaxPerPage)=0 then
           currentpage= totalPut / MaxPerPage
         else
            currentpage= totalPut / MaxPerPage + 1
         end if
          end if

           if currentPage=1 then
               showContent
               showpage totalput,MaxPerPage,"shoukuandan.asp"
           else
              if (currentPage-1)*MaxPerPage<totalPut then
                rs.move (currentPage-1)*MaxPerPage
                dim bookmark
                bookmark=rs.bookmark
                showContent
                 showpage totalput,MaxPerPage,"shoukuandan.asp"
            else
             currentPage=1
                showContent
                showpage totalput,MaxPerPage,"shoukuandan.asp"
           end if
        end if
              end if

       sub showContent
          dim i
       i=0%>
<table width="1024" border="1" align="center" cellspacing="0" bgcolor="#ced7f7">
<tr onMouseOver=mouseOver(this,'999999');   onMouseOut=mouseOut(this,'#ced7f7');>
    <td width="10%" align="center">出货日期</td>
    <td width="11%" align="center">出货单</td>
    <td width="19%" align="center">客户名称</td>
    <td width="11%" align="center">总额</td>
    <td width="15%" align="center">收款金额</td>
    <td width="10%" align="center">收款时间</td>
    <td width="13%" align="center">备注</td>
    <td width="11%" align="center">完成</td>
</tr>
   <%
     do while not rs.eof%>
<tr onMouseOver=mouseOver(this,'999999');   onMouseOut=mouseOut(this,'#ced7f7');>
    <td align="center"><%'=rs("chuhuodate")%></td>
    <td align="center"><a href="editshoukuandan.asp?chuhuodan=<%=rs("chuhuodan")%>" target="_blank"><%=rs("chuhuodan")%></a></td>
    <td align="center"><%'=rs("company")%></td>
    <td align="right"><%'=formatnumber(rs("zonger"),2,-1)%>  </td>
    <td align="right"><%'=formatnumber(rs("shoukuanjiner"),2,-1)%>  </td>
    <td align="center"><%'=rs("shoukuandate")%> </td>
    <td align="center"> <%'=rs("beizhu")%></td>
    <td align="center"></td>
</tr>
   <%i=i+1
   if i>=MaxPerPage then Exit Do
   rs.movenext
   loop
   %>

<tr onMouseOver=mouseOver(this,'999999');   onMouseOut=mouseOut(this,'#ced7f7');>
    <td align="center"> </td>
    <td align="center"> </td>
    <td align="center">合计</td>
    <td align="right"><%'=formatnumber(sum1,2,-1)%>  </td>
    <td align="right">实收合计  <%'=formatnumber(sum2,2,-1)%>  </td>
    <td align="center">优惠</td>
    <td align="right"><%'=sum3-sum4%> </td>
    <td align="center"> </td>
</tr>
<tr onMouseOver=mouseOver(this,'999999');   onMouseOut=mouseOut(this,'#ced7f7');>
    <td align="center"> </td>
    <td align="center"> </td>
    <td align="center">应收款</td>
    <td align="right"><%'=sum7%>   </td>
    <td align="right"> </td>
    <td align="center"> </td>
    <td align="right">  </td>
    <td align="center"> </td>
</tr>
   <%

   rs.close
   set rs=nothing%>
</table>

</html>