学生成绩记录排名次-----请教

CREATE #TEMP ( ID INT IDENTITY(1,1), fenshu INT, xingming ) SELECT fenshu, xingming INTO #TEMP FROM chengji ORDER BY fenshu 　 SELECT fenshu, xingming, 　　　mingci = '第'+str([ID])+'名' FROM #TEMP DROP #TEMP

create table #t (fs int,id int identity (1,1)) insert into #t(fs) select distinct fenshu from chengji order by fenshu desc select cj.*,'第'+cast(#t.id as varchar)+'名' from chengji cj join #t on #t.fs=cj.fenshu order by #t.id drop table #t

Create proc sp_SChengjiInfo AS CREATE #TEMP ( ID INT IDENTITY(1,1), fenshu INT, xingming ) SELECT fenshu, xingming INTO #TEMP FROM chengji ORDER BY fenshu 　 SELECT fenshu, xingming, 　　　mingci = '第'+str([ID])+'名' FROM #TEMP DROP #TEMP

using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; public partial class _paiming : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { Bind(); } } private void Bind() { using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ToString())) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "sp_SChengJiInfo"; //指定存储过程名字 cmd.CommandType = CommandType.StoredProcedure; //指定类型为存储过程 cmd.Connection = con; SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); sda.Fill(ds); this.gvChengJi.DataSource = ds.Tables[0].DefaultView; this.gvChengJi.DataBind(); ds.Dispose(); } } }

加为好友

发送私信

在线聊天

shinevi
边磨边练
等级:

发表于：2008-04-10 09:28:3823楼得分:1

存储过程同13楼,对应程式14楼;
sql语句同20楼,对应程式如下:

aspx界面:
...
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="xingming" HeaderText="姓名" SortExpression="xingming" />
<asp:BoundField DataField="fenshu" HeaderText="分数" SortExpression="fenshu" />
<asp:BoundField DataField="paiming" HeaderText="排名" SortExpression="paiming" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
...

apsx.cs后台:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _paiming : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
GridView1_Bind();
}
}

private void GridView1_Bind()
{
string myconn = ConfigurationManager.ConnectionStrings["ConStr"].ToString();//web.config
SqlConnection conn = new SqlConnection(myconn);
conn.Open();
string sqlstr = "select xingming,fenshu,paiming='第'+convert(varchar(20),(select count(b.fenshu)+1 from "
+ "(select distinct fenshu from chengji where fenshu>a.fenshu)b))+'名' from chengji a order by paiming";
SqlDataAdapter da = new SqlDataAdapter(sqlstr,conn);
DataSet ds = new DataSet();
da.Fill(ds,"chengji");
GridView1.DataSource = ds.Tables["chengji"].DefaultView;
GridView1.DataBind();
}
}

web.config添加:
<connectionStrings>
<add name="ConStr" connectionString="Data Source=你的计算机名;Initial Catalog=你的数据库;Persist Security Info=True;User ID=数据库用户名;Password=数据库密码" providerName="System.Data.SqlClient"/>
</connectionStrings>

修改删除举报引用回复