62,025
社区成员
发帖
与我相关
我的任务
分享
create procedure KF_pro_Conversations
(@startIndex int,
@endIndex int,
@user_name nvarchar(50)
)
as
set nocount on
declare @indextable table(id int identity(1,1),nid int)--临时表有两个字段,一个是id,还有一个是nid,用临时表解决主表id不安顺序增长
set rowcount @endIndex
insert into @indextable(nid) select msg_id from KF_1_MessageInfo where senderName=@user_name and msg_id in (select max(msg_id) from KF_1_MessageInfo group by ReceiverName,senderName)
select O.ReceiverName,b.notes_name
from KF_1_MessageInfo as O
left join KF_userlogin b on O.ReceiverName=b.loginuser_ip
inner join @indextable t on O.msg_id=t.nid
where t.id between @startIndex and @endIndex and senderName=@user_name and msg_id in (select max(msg_id) from KF_1_MessageInfo group by ReceiverName,senderName)
create procedure KF_pro_Conversations
(@startIndex int,
@endIndex int,
@user_name nvarchar(50),
@Tablename nvarchar(50)
)
as
begin
set nocount on
declare @indextable table(id int identity(1,1),nid int)--临时表有两个字段,一个是id,还有一个是nid,用临时表解决主表id不安顺序增长
set rowcount @endIndex
declare @sql1 varchar(255)
declare @sql2 varchar(255)
select @sql1='insert into '+@indextable+'(nid) select msg_id from'+ @Tablename+' where senderName='+@user_name+' and msg_id in (select max(msg_id) from'+ @Tablename+' group by ReceiverName,senderName)'
exec(@sql1)
select @sql2 ='select O.ReceiverName,b.notes_name
from'+ @Tablename+' as O
left join KF_userlogin b on O.ReceiverName=b.loginuser_ip
inner join '+@indextable+' t on O.msg_id=t.nid
where t.id between '+@startIndex+' and '+@endIndex+' and senderName='+@user_name+' and msg_id in (select max(msg_id) from'+ @Tablename+' group by ReceiverName,senderName)'
exec(@sql2)
end
ALTER procedure [dbo].[proc_paged_with_notin] --利用select top and select not in
(
@pageIndex int, --页索引
@pageSize int --每页记录数
)
as
begin
set nocount on;
declare @timediff datetime --耗时
declare @sql nvarchar(500)
select @timediff=Getdate()
set @sql='select top '+str(@pageSize)+' * from tb_TestTable where(ID not in(select top '
+str(@pageSize*@pageIndex)+' id from tb_TestTable order by ID ASC)) order by ID'
execute(@sql) --因select top后不支技直接接参数,所以写成了字符串@sql
select datediff(ms,@timediff,GetDate()) as 耗时
set nocount off;
end