asp,sql高手请指教

memewa1 2009-07-30 09:27:26
有两个表,A表,B表,里面的内容如下:
A:北京、上海、四川。。。。。。。
B:1.张三、北京、600分
2.李四、北京、577分
3.王五、上海、610分
。。。。。。。。。。。。。。。。。。

要实现以下效果以表格的形式,

北京 总分:(所有隶属北京学生的分数相加)
张三 600分
李四 577分
。。。。。。。。。。。。
上海 总分(所有隶属北京学生的分数相加)
王五 610分
。。。。。。。。。。。。。。。
四川
。。。。。。。。。。。。。。。。。。
...全文
60 点赞 收藏 10
写回复
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
Dogfish 2009-07-31
set rs = CreateObject("ADODB.Recordset")
rs.Open "select * from table order by 地区, 成绩 desc", conn
response.write "<table>"
area = ""
area1 = ""
do while not rs.EOF
if area <> rs("地区") then
area = rs("地区")
area1 = area
else
area1 = ""
end if
if area1 <> "" then
response.write "<tr>"
response.write "<td>" & area1 & "</td>"
response.write "<td></td>"
response.write "<td>总分</td>"
response.write "</tr>"
end if
response.write "<tr>"
response.write "<td></td>"
response.write "<td>" & rs("姓名") & "</td>"
response.write "<td>" & rs("总分") & "</td>"
response.write "</tr>"
rs.movenext
wend
response.write "</table>"
回复
王者coco 2009-07-31
up
回复
野人丶嘎嘎 2009-07-31
楼上所言极是
回复
hongmaohouzi 2009-07-31
set rs = CreateObject("ADODB.Recordset")
rs.Open "select * from table order by 地区, 成绩 desc", conn
response.write "<table>"
area = ""
area1 = ""
do while not rs.EOF
if area <> rs("地区") then
area = rs("地区")
area1 = area
else
area1 = ""
end if
if area1 <> "" then
response.write "<tr>"
response.write "<td>" & area1 & "</td>"
response.write "<td></td>"
response.write "<td>总分</td>"
response.write "</tr>"
end if
response.write "<tr>"
response.write "<td></td>"
response.write "<td>" & rs("姓名") & "</td>"
response.write "<td>" & rs("总分") & "</td>"
response.write "</tr>"
rs.movenext
wend
response.write "</table>"
回复
lzp4881 2009-07-31
请参考这个帖子,你的问题跟他差不多
http://topic.csdn.net/u/20080610/17/789b65a4-064e-4c3e-952a-fd59b3e65ee9.html
回复
hookee 2009-07-30
access的话
oConn.Provider = "MSDataShape"
oConn.ConnectionString = "一般连接串"
回复
hookee 2009-07-30
用DataShap,很简单
以下是sql server的


<%
Set oConn = CreateObject("ADODB.Connection")
sConn = "Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=127.0.0.1;Initial Catalog=SENSTAR;User ID=sa;Password=sa;"
oConn.Open sConn
sql = "SHAPE{SELECT place FROM A}" &_
"APPEND({SELECT name, place, score FROM B} AS STU " &_
"RELATE place TO place)," &_
"({SELECT place, SUM(score) AS ttl FROM B GROUP BY place} AS SM " &_
"RELATE place TO place)"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sql, oConn
Do While Not oRS.EOF
Set oRS2 = oRS("SM").Value
Response.Write "<BR>"
Response.Write oRS("place") & "   " & oRS2("ttl") & "<br>"
Set oRS1 = oRS("STU").Value
DO While Not oRS1.EOF
Response.Write oRS1("name") & "     " &_
oRS1("place") & "     " &_
oRS1("score") & "     " & "<BR>"
oRS1.MoveNext
Loop
oRS.MoveNext
Loop
oRS.close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
%>
回复
http://www.myziy.com/show.php?contentid=151
看看这个例子程序
回复
北京不不 2009-07-30
用ASP去分吧
先排好序select * from table order by adrs (假设adrs就是地区字段)
记录上条记录的地区名称,如果名称不一样,就输出地区名称,再显示记录
dim
adrs=""
do while not rs.eof
if rs("adrs")<>adrs then
response.write rs("adrs") '如果地区和上条记录地区不同,显示地区名
end if
response.write rs("姓名")&rs("分数")&"<br>" '输出记录
adrs=rs("adrs")
rs.movenext()
loop
回复
算总分这个不好用SQL算
不过可以利用程序实现
回复
发动态
发帖子
ASP
创建于2007-09-28

2.8w+

社区成员

ASP即Active Server Pages,是Microsoft公司开发的服务器端脚本环境。
申请成为版主
社区公告
暂无公告