select A,B,C,D,E
from (select A,B,C,sum(D) as D,sum(E) as E,'0' as flag
from aaa
group by A,B,C
union
select A,B,C,D,E,'1' as flag
from aaa ) new
order by A,B,C,flag
--然后再asp中逐行显示就可以了.
from (select A,B,C,sum(D) as D,sum(E) as E,'0' as flag
from aaa
group by A,B,C
union
select A,B,C,D,E,'1' as flag
from aaa ) new
order by A,B,C,flag
--然后再asp中逐行显示就可以了.
sql="Select * from (select A,Min(B) as B,Min(C) as C,sum(D) as D,sum(E) as E, from aaa group By A) A order by A desc "另外desc是不是多余了,如果反序的话,那么明细表也应该反序咯。sql1应该是查明细情况sql1="select A,B,C,D,E from aaa order by A desc";代码大概如下:do while not rst1.EOF and rst1("A")=rs("A")
'print rst1 ...
rst1.movenext;
wend
但是要求分组统计的小计排在明细的前面
用flag排序,怎么知道0就是最小值呢,是不是太主观了
有负值怎么办,最小取多小?!
支持 rivery 哈哈
A B C D E(列名)1 A A C 1
1 A A D 2
1 A A E 3
2 B B F 1
2 B B G 2要显示成这样的表格
1 A A C 6(前3列相同取任意一值,D列也显示任一值或忽略,E列求和)(第一行)
—————(分割线)
1 A A C 1(第一行的明细第一行)(第二行)
1 A A D 2(第一行的明细第二行)(第三行)
1 A A E 3(第一行的明细第三行)(第四行)
—————(分割线)
2 B B F 3(前3列相同取任意一值,D列也显示任一值或忽略,E列求和)(第五行)
—————(分割线)
2 B B F 1(第五行的明细第一行)(第六行)
2 B B G 2(第五行的明细第二行)(第七行)
—————(分割线)ASP文件代码 <%(连接数据库文件略)
dim sql,sql1
dim rs,rst1
sql="Select * from (select A,Min(B) as B,Min(C) as C,sum(D) as D,sum(E) as E, from aaa group By A) A order by A desc "
Set rs= Server.CreateObject("ADODB.Recordset")
rs.open sql,conn,1,1sql1="?"
Set rs1= Server.CreateObject("ADODB.Recordset")
rs1.open sql1,conn,1,1%><%do while NOT rst.EOF%><%=rs("A")%><%=rs("B")%><%=rs("C")%><%=rs("D")%><%=rs("E")%>
—————(分割线)
<%?%>
—————(分割线)
<%rs.MoveNext
loop
rs.close
%>
<%(连接数据库文件略)
dim sql,sql1
dim rs,rst1
sql="Select * from (select A,Min(B) as B,Min(C) as C,sum(D) as D,sum(E) as E, from aaa group By A) A order by A desc "
Set rs= Server.CreateObject("ADODB.Recordset")
rs.open sql,conn,1,1Set rs1= Server.CreateObject("ADODB.Recordset")
%><%do while NOT rst.EOF%><%=rs("A")%><%=rs("B")%><%=rs("C")%><%=rs("D")%><%=rs("E")%><%
sql1="select * from aaa where a=" & rs("A")
rs1.open sql,conn,3,1
for i=1 to rs1.recordcount
if not rs1.eof then
%>
<%=rs1("A")%><%=rs1("B")%><%=rs1("C")%><%=rs1("D")%><%=rs1("E")%>
<%
rs1.movenext
end if
next
%>