/ * - Originally: http://community.9cbs.net/expert/topic/3845/3845647.xml? Temp = .7272455 - * /
/ * - Treatment requirements
Random sorting with the following statement: Select * from xiaofei where status = 1 Order by newid ()
This will change each time the user refreshed. Now I want to do the sort of different users coming in and seeing, but the same user is the same for each refresh page for a while. - * / GO
/ * - processing analysis
With a temporary table cache sort result, then combine the original primary key to query the data - * /
- General Process Create Proc P_Qry @ UserName SysName, - User Name, Depending on the username to set the sequenual table @Pagesize Int = 5, - size @currentPage Int = 1 - current page ,> = 1 means normal query, <1 means reconstructing temporary table Asset NoCount ON
Declare @tbname sysnameset @ TBNAME = quotename ('##' @ username)
- If the temporary table does not exist, or @currentpage <1, generate the processing temporary table if Object_id (@tbname) is null or isnull (@ currentpage, 0) <1begin - If the temporary table already exists, first delete it if Object_id (@TBNAME) Is Not Null EXEC ('Drop Table' @ TBNAME)
- Hypothesis Table of the primary key field name: ID EXEC ('select nnid = Identity (Bigint, 0, 1), * from (select top 100 percent id from xiaofei where status = 1 Order by newid ()) a') set @ CurrentPage = 1END
- Data declare @s nvarchar (4000) set @S nvarchar (4000) set @ s = 'select a. * from xiaofei a,' @ tbname 'bwhere a.id = B.ID and B.NNID BETWEEN (@currentpage -1) * @ PageSize 1nd @ currentpage * @ Pagesize'Exec sp_executesql @ s, n '@ currentpage int, @ pagesize int', @ currentpage, @ Pagesize
