关于新闻的'上一条''下一条'

[lutianling521]

如题！效率高点好！

[kfps8kfps8]

根据新闻的创建时间来取

[wuyq11]

CREATE proc PNArticle
@ID varchar(8)
declare @Sql varchar(500),declare @Sql2 varchar(500)
SET @Sql = 'select top 1 * from Table1 where 1=1 '
set @Sql=@Sql+' and XTBH<'+@ID+''
set @Sql=@Sql+'order by ID DESC'

SET @Sql2 = 'select top 1 * from Table1 where 1=1 '
set @Sql2=@Sql+' and XTBH>'+@ID+''
set @Sql2=@Sql+'order by ID '

BEGIN
EXECUTE (@Sql)
EXECUTE (@Sql2)
END
GO

[greymouseyu]

CategoryID   Name       Description
1	Love & Romance	Here's our collection of balloons with romantic messages.
2	Birthdays	Tell someone "Happy Birthday" with one of these wonderful balloons!
3	Weddings	Going to a wedding? Here's a collection of balloons for that special event!
4	Message Balloons	Why write on paper, when you can deliver your message on a balloon?
5	Cartoons	Buy a balloon with your child's favorite cartoon character!
6	Miscellaneous	Various baloons that your kid will most certainly love!

上一条：

WITH tbs AS
(
SELECT [CategoryID]
      ,[Name]
      ,[Description]
	  ,ROW_NUMBER() OVER(ORDER BY [Name]) as row
  FROM [Category]
)

select * from tbs where row=(select row-1 from tbs where CategoryID=5)

[greymouseyu]

select * from tbs

CategoryID   Name       Description                                                             row
2	Birthdays	Tell someone "Happy Birthday" with one of these wonderful balloons!	1
5	Cartoons	Buy a balloon with your child's favorite cartoon character!	2
1	Love & Romance	Here's our collection of balloons with romantic messages.	3
4	Message Balloons	Why write on paper, when you can deliver your message on a balloon?	4
6	Miscellaneous	Various baloons that your kid will most certainly love!	5
3	Weddings	Going to a wedding? Here's a collection of balloons for that special event!	6

select * from tbs where row=(select row+1 from tbs where CategoryID=5)

CategoryID   Name       Description                                                     row
1	Love & Romance	Here's our collection of balloons with romantic messages.	3

[lutianling521]

[Quote=引用 2 楼 wuyq11 的回复:]
CREATE proc PNArticle
@ID varchar(8)
declare @Sql varchar(500),declare @Sql2 varchar(500)
SET @Sql = 'select top 1 * from Table1 where 1=1 '
set @Sql=@Sql+' and XTBH <'+@ID+''
set @Sql=@Sql+'order by ID DESC'

SET @Sql2 = 'select top 1 * from Table1 where 1=1 '
set @Sql2=@Sql+' and XTBH>'+@ID+''
set @Sql2=@Sql+'order by ID '

BEGIN
EXECUTE (@Sql)
EXECUTE (@Sql2)
E…
[/Quote]
创建时
消息 156，级别 15，状态 1，过程 PNArticle，第 4 行
关键字 'declare' 附近有语法错误。
我用的是sql2005

[saisky]

[Quote=引用 2 楼 wuyq11 的回复:]
CREATE proc PNArticle
@ID varchar(8)
declare @Sql varchar(500),declare @Sql2 varchar(500)
SET @Sql = 'select top 1 * from Table1 where 1=1 '
set @Sql=@Sql+' and XTBH <'+@ID+''
set @Sql=@Sql+'order by ID DESC'

SET @Sql2 = 'select top 1 * from Table1 where 1=1 '
set @Sql2=@Sql+' and XTBH>'+@ID+''
set @Sql2=@Sql+'order by ID '

BEGIN
EXECUTE (@Sql)
EXECUTE (@Sql2)
E…
[/Quote]

TOP 的效率还是不错的

[happy664618843]

顶顶啊!

[巴掌锅]

绑定！！！

[happy1111myself]

学习了

[lutianling521]

[Quote=引用 5 楼 lutianling521 的回复:]
引用 2 楼 wuyq11 的回复:
CREATE proc PNArticle
@ID varchar(8)
declare @Sql varchar(500),declare @Sql2 varchar(500)
SET @Sql = 'select top 1 * from Table1 where 1=1 '
set @Sql=@Sql+' and XTBH <'+@ID+''
set @Sql=@Sql+'order by ID DESC'

SET @Sql2 = 'select top 1 * from Table1 where 1=1 '
set @Sql2=@Sql+' and XTBH>'+@ID+''
set @Sql2=@Sql+'order by ID '

BEGIN
EXECUTE (@…
[/Quote]
up

[niitnanfeng]

顶

[中年秃头大叔]

前台:
上一条：<asp:HyperLink ID="hyperUp" runat="server"></asp:HyperLink>
下一条：<asp:HyperLink ID="hyperDown" runat="server"></asp:HyperLink>
后台:
protected void BindUp()//上一条
{
SqlParameter parID=new SqlParameter("@id",Request.QueryString["id"].Trim());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, "select top 1 title,min(id) as id from news_table where id>@id group by title order by id", parID))
{
if (reader.Read())
{
hyperUp.Text = reader["title"].ToString().Trim();
hyperUp.NavigateUrl = "news.aspx?id=" + reader["id"].ToString() + "";
}
}
}
protected void BindDown()//下一条
{
SqlParameter parID = new SqlParameter("@id", Request.QueryString["id"].Trim());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, "select top 1 title,max(id) as id from news_table where id<@id group by title order by id desc", parID))
{
if (reader.Read())
{
hyperDown.Text = reader["title"].ToString().Trim();
hyperDown.NavigateUrl = "news.aspx?id=" + reader["id"].ToString() + "";
}
}
}
数据库新闻表news_table字段有:
id 新闻编号 取当前日期，精确到毫秒
title标题
content内容
in_date录入时间
排序为先以in_date降序，再以id升序。id为主键
在news.aspx这个页面的
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
try
{
indUp();
BindDown();
}
catch
{
throw new Exception("数据载入失败！");
}
}
}
就可以了。