try:
set nocount on
declare @sqlStr nvarchar(4000)
set @sqlStr='select Id,[Name],Phone,Address,CommentNumber,ClickTimes,RecentId,RecentHead,RecentContent,floor((Rate-0.25)*2)/2+0.5 Rate
from ShopsList where type='''+@type+''' and city='''+@cityid+''' order by Rate desc'
declare @P1 int,@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
set @pageindex=(@pageindex-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@pageindex,@pagesize
exec sp_cursorclose @P1
set nocount on
declare @sqlStr nvarchar(4000)
set @sqlStr='select Id,[Name],Phone,Address,CommentNumber,ClickTimes,RecentId,RecentHead,RecentContent,floor((Rate-0.25)*2)/2+0.5 Rate
from ShopsList where type='''+@type+''' and city='''+@cityid+''' order by Rate desc'
declare @P1 int,@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
set @pageindex=(@pageindex-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@pageindex,@pagesize
exec sp_cursorclose @P1
set nocount on
declare @sqlStr nvarchar(4000)
set @sqlStr='select Id,[Name],Phone,Address,CommentNumber,ClickTimes,RecentId,RecentHead,RecentContent,floor((Rate-0.25)*2)/2+0.5 Rate
from ShopsList where type='+@type+' and city='+@cityid+' order by Rate desc'
declare @P1 int,@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
set @pageindex=(@pageindex-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@pageindex,@pagesize
exec sp_cursorclose @P1
set nocount on
declare @sqlStr nvarchar(4000)
set @sqlStr='select Id,[Name],Phone,Address,CommentNumber,ClickTimes,RecentId,RecentHead,RecentContent,floor((Rate-0.25)*2)/2+0.5 Rate
from ShopsList where type='+cast(@type as varchar(32))+' and city='+cast(@cityid as varchar(32))+' order by Rate desc'
declare @P1 int,@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
set @pageindex=(@pageindex-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@pageindex,@pagesize
exec sp_cursorclose @P1
(@Type TinyInt,
@CityId SmallInt,
@pagesize int,
@pageindex int)
as
begin
declare @sqlStr nvarchar(4000)
declare @P1 int,@rowcount int
set @sqlStr='select Id,[Name],Phone,Address,CommentNumber,ClickTimes,RecentId,RecentHead,RecentContent,floor((Rate-0.25)*2)/2+0.5 Rate
from ShopsList where type='+cast(@type as varchar)+' and city='+cast(@cityid as varchar)+' order by Rate desc'
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
set @pageindex=(@pageindex-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@pageindex,@pagesize
exec sp_cursorclose @P1
end
这样建立,你测试下行不行,这是通过系统提供的存储过程来实现,但用到游标
你需要解释哪里呢
==================
视图可以建立索引
加索引看看
SET ANSI_NULLS ONGOSET ANSI_PADDING ONGOSET ANSI_WARNINGS ONGOSET QUOTED_IDENTIFIER ONGOSET NUMERIC_ROUNDABORT OFFGO
都写了 ,是不是 不能在 AVG() 上面建立索引啊 ?
搞得现在是按 点击数 倒叙排列,而且还不算快!这个问题怎么解决啊??? 救命!!!1