主页

asp,sql高手请指教

memewa1 2009-07-30 09:27:26

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

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

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

...全文

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

回复赞

野人丶嘎嘎 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

%>

回复赞

一把编程的菜刀 2009-07-30

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

回复赞

一把编程的菜刀 2009-07-30

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

回复赞

发动态

发帖子

ASP

创建于2007-09-28

加入

2.8w+

社区成员

35.7w+