• 主页

# 150分再求三个排名语句

ninefive 2004-04-06 02:40:20

--------------------------------------

1）班级平均分，在全校所有班级中的统一排名
2）专业平均分，在全校所有专业中的统一排名
3）院系平均分，在全校所有院系中的统一排名

1）班级 专业 院系 平均分 排名
2）专业 院系 平均分 排名
3）院系 平均分 排名
...全文
14 点赞 收藏 28

28 条回复

ninefive 2004-04-10

ninefive 2004-04-09

<table>
<tr>
<td colspan="6"><strong><font color="#FF00FF">班    级    排    名</font></strong></td>
</tr>
<tr align="center">
<td width="10%">年级</td>
<td width="10%">班级</td>
<td width="31%">专业</td>
<td width="31%">院系</td>
<td width="10%">平均分</td>
<td width="8%">排名</td>
</tr>
</table>
<%
sql="select sum(score)/count(bj) as pjf,bj from score where Selected=1 group by bj order by sum(score)/count(bj) desc"
rs.open sql,conn,1,3
score=0
paiming=0
do while not rs.eof
if score<>rs("pjf") then
score=rs("pjf")
paiming=paiming+1
end if
bj=rs("bj")
sql1="select * from score where bj='"&bj&"' and Selected=1" '找出全班所有的人數
rs1.open sql1,conn,1,3
%>
<table width="90%" height="2" border="0" align="center">
<tr>
<td></td>
</tr>
</table>
<table>
<tr align="center">
<td width="10%">
<%response.write rs1("nj")%>
</td>
<td width="10%">
<%response.write bj%>
</td>
<td width="31%">
<%response.write rs1("zy")%>
</td>
<td width="31%">
<%response.write rs1("ys")%>
</td>
<td width="10%">
<%response.write FormatNumber(rs("pjf"))%>
</td>
<td width="8%">
<%response.write paiming%>
</td>
</tr>
</table>
<%rs.movenext
loop %>

onlytiancai 2004-04-09

hchxxzx 2004-04-09

select bj,score from (select bj,sum(score)/count(*) as score from tab group by bj) order by score

hchxxzx 2004-04-09

hchxxzx 2004-04-09

select bj,score from (select bj,sum(score)/count(*) as score from tab order by bj) order by score

cjf1009 2004-04-08

snowcloud2002 2004-04-07
up

ninefive 2004-04-07

cjf1009 2004-04-07
<%sql="select sum(score)/count(bj) as pjf,bj from table where Selected='1'group by bj order by pjf desc" '按班及分組,算平均分,班級名字,按平均分排名
rs.open sql,conn,1,3

score=0
paiming=0
do while not rs.eof
if score<>rs("pjf") then
score=rs("pjf")
paiming=paiming+1
end if'按楼主的要求，这里应该这样

ninefive 2004-04-07
UP!

<%sql="select sum(score)/count(bj) as pjf,bj from table where Selected='1'group by bj order by pjf desc" '按班及分組,算平均分,班級名字,按平均分排名
rs.open sql,conn,1,3
paiming=0
do while not rs.eof
paiming=paiming+1
bj=rs("bj")
sql1="select * from table where bj='"&bj&"' where Selected='1'" '找出這個班所有的人數
rs1.open sql1,conn,1,3
tmp1=rs1.recordcount
pjf=rs("sum1")/tmp1 '班級平均分 %>
<table><tr>
<td><%response.write bj%></td>
<td><%response.write rs1("zy")%></td>
<td><%response.write rs1("ys")%></td>
<td><%response.write pjf%></td>
<td><%response.write paiming%></td>
</tr></table>
<%rs.movenext
loop %>
sql server 2000下通過.

1.select sum(score) as sum1,bj from table where Selected='1'group by bj ,通過?
2.select sum(score) as sum1,count(bj) as sum2,bj from table where Selected='1'group by bj ,通過?

3.select sum(score)/count(bj) as pjf,bj from table where Selected='1'group by bj order by pjf desc ,通過?

caoshangfei 2004-04-06

cjf1009 2004-04-06
sql语句xieyj(Free Fish) 已经写出来了，不过排名，按你说的那种，估计要asp写出来，因为你的库里没有排名字段，显示的时候用循环，赋值记录这个值，然后跟下一个值比较，相同就不变，变了就+1。

xieyj 2004-04-06
bj:
select sum(score)/count(bj) as test,bj from students group by bj order by sum(score)/count(bj)

zy:
select sum(score)/count(zy) as test,zy from students group by zy order by sum(score)/count(zy)

ys:
select sum(score)/count(ys) as test,ys from students group by ys order by sum(score)/count(ys)

xxrl 2004-04-06

ninefive 2004-04-06

sql="select sum(score) as sum1,bj from score where Selected=1 group by bj order by sum1 desc" '按班及分組,算總分,班級名字,按總分排名
rs.open sql,conn,1,3

Microsoft VBScript 编译器错误 错误 '800a03f6'

/iisHelp/common/500-100.asp，行242

Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e10'

[Microsoft][ODBC Microsoft Access Driver] 参数不足，期待是 1。

/xuanchuanbu/bjpm.asp，行5

1）班级平均分，在全校所有班级中的统一排名
<%sql="select sum(score) as sum1 ,bj from table where Selected='1'group by bj order by sum1 desc" '按班及分組,算總分,班級名字,按總分排名
rs.open sql,conn,1,3
paiming=0
do while not rs.eof
paiming=paiming+1
bj=rs("bj")
sql1="select * from table where bj='"&bj&"' where Selected='1'" '找出這個班所有的人數
rs1.open sql1,conn,1,3
tmp1=rs1.recordcount
pjf=rs("sum1")/tmp1 '班級平均分 %>
<table><tr>
<td><%response.write bj%></td>
<td><%response.write rs1("zy")%></td>
<td><%response.write rs1("yx")%></td>
<td><%response.write pjf%></td>
<td><%response.write paiming%></td>
</tr></table>
<%rs.movenext
loop %>

ninefive 2004-04-06

hujing008 2004-04-06

ASP

2.8w+

ASP即Active Server Pages，是Microsoft公司开发的服务器端脚本环境。