strSql="CREATE TABLE #tmp_table (id int IDENTITY (1, 1) PRIMARY KEY, username nvarchar (50), prv nvarchar (50), money int DEFAULT (0))"
strSq=strSql+"; INSERT INTO #tmp_table (username, prv, money) SELECT TOP 10 username, prv, money FROM my_table ORDER BY money"
这是建临时表并填充数据,剩下的你自己能搞定了吧。
<%
rs.open "select distinct prv from aa",cn
do while not rs.eof
prv = rs("prv")
money = 0
sql = "select top 10 money from tableaa where prv = '"&prv&"'
rs1.open sql,cn
do while not rs1.eof
money = money + rs1("money")
rs1.movenext
loop
ary("&prv&") = money
rs.movenext
loop%>
--我这里只测试了top 2,而且测试的数据不多,你试下
--测试数据
create table aa(username nvarchar(20),prv nvarchar(20),[money] int)
insert into aa
select 'a','浙江',2
union all
select 'b','浙江',3
union all
select 'c','上海',4
union all
select 'd','上海',5
union all
select 'e','浙江',5
select * from aa
select distinct prv,
(select sum(d.[money]) from (select top 2 * from aa c where c.prv=b.prv order by c.[money] desc)d ) as guideline
from aa b
order by guideline desc
--删除测试数据
drop table aa
select distinct prv,
(select sum(d.[money]) from (select top 2 * from aa c where c.prv=b.prv order by c.[money] desc)d ) as guideline
from aa b
order by guideline desc
select distinct prv,
(select sum(d.[money]) from (select top 2 * from aa c where c.prv=b.prv order by c.[money] desc)d ) as guideline
from aa b
order by guideline desc