asp,sql高手请指教

[memewa1]

有两个表，A表，B表,里面的内容如下：
A:北京、上海、四川。。。。。。。
B：1.张三、北京、600分
2.李四、北京、577分
3.王五、上海、610分
。。。。。。。。。。。。。。。。。。

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

北京 总分：（所有隶属北京学生的分数相加）
张三 600分
李四 577分
。。。。。。。。。。。。
上海 总分（所有隶属北京学生的分数相加）
王五 610分
。。。。。。。。。。。。。。。
四川
。。。。。。。。。。。。。。。。。。

[一把编程的菜刀]

算总分这个不好用SQL算
不过可以利用程序实现

[北京不不]

用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

[一把编程的菜刀]

http://www.myziy.com/show.php?contentid=151
看看这个例子程序

[hookee]

用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
%>

[hookee]

access的话
oConn.Provider = "MSDataShape"
oConn.ConnectionString = "一般连接串"

[lzp4881]

请参考这个帖子，你的问题跟他差不多
http://topic.csdn.net/u/20080610/17/789b65a4-064e-4c3e-952a-fd59b3e65ee9.html

[hongmaohouzi]

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]

up

[Dogfish]

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>"