分页给以下这条参考参考,但以下的有点问题SELECT TOP 10 a.Co_ID, a.Co_Name, a.User_Name, b.User_Power, a.Co_Createtime,
a.Co_Products, a.Co_Logo, a.Province_ID
FROM Opto_Company a INNER JOIN
Opto_User b ON a.User_Name = b.User_Name
WHERE (a.Co_Audit = 1) AND (a.Lang_Type = 0) AND (a.Co_Createtime <=
(SELECT MIN(Co_Createtime)
FROM (SELECT TOP 10 Co_Createtime
FROM Opto_Company ac INNER JOIN
Opto_User bc ON ac.User_Name = bc.User_Name
WHERE ac.Co_Infograde = 1
ORDER BY CONVERT(varchar(10), ac.Co_Createtime, 121) DESC,
bc.User_Power DESC) AS tbltmp)) AND (a.Co_Infograde = 1)
ORDER BY CONVERT(varchar(10), a.Co_Createtime, 121) DESC, b.User_Power DESC
a.Co_Products, a.Co_Logo, a.Province_ID
FROM Opto_Company a INNER JOIN
Opto_User b ON a.User_Name = b.User_Name
WHERE (a.Co_Audit = 1) AND (a.Lang_Type = 0) AND (a.Co_Createtime <=
(SELECT MIN(Co_Createtime)
FROM (SELECT TOP 10 Co_Createtime
FROM Opto_Company ac INNER JOIN
Opto_User bc ON ac.User_Name = bc.User_Name
WHERE ac.Co_Infograde = 1
ORDER BY CONVERT(varchar(10), ac.Co_Createtime, 121) DESC,
bc.User_Power DESC) AS tbltmp)) AND (a.Co_Infograde = 1)
ORDER BY CONVERT(varchar(10), a.Co_Createtime, 121) DESC, b.User_Power DESC
a.Co_Products, a.Co_Logo, a.Province_ID
FROM Opto_Company a INNER JOIN
Opto_User b ON a.User_Name = b.User_Name
WHERE (a.Co_Audit = 1) AND (a.Lang_Type = 0) AND
(a.Co_Infograde = 1)
ORDER BY CONVERT(varchar(10), a.Co_Createtime, 121) DESC, b.User_Power DESC在ASP里通过pageSize=10控制每页最大显示记录数
@pageIndex int, --以1开始
@pageSize int
as
select identity(int,1,1) as ID
,t.*
into #
from company t
order by convert(char(10),caddtime,120) desc
,(select userpower from [user] where username=t.username) descdeclare @sql varchar(8000)
set @sql=' select top '+convert(varchar,@pagesize)
+' * from # where ID>'+convert(varchar,(@pageIndex-1)*@pagesize)
exec(@sql)drop table #
go
@pageIndex int, --以1开始
@pageSize int
as
declare @tb table
(
ID int identity,
coid int,
coname varchar(20),
username varchar(20),
coaudit int,
cograde int,
caddtime datetime
)insert @tb(coid,coname,username,coaudit,cograde,caddtime)
select *
from company t
order by convert(char(10),caddtime,120) desc
,(select userpower from [user] where username=t.username) descset rowcount @pageSize --选取指定数量记录select coid,coname,username,coaudit,cograde,caddtime
from @tb
where ID>(@pageIndex-1)*@pagesizeset rowcount 0go
(
ID int identity,
coid int,
coname varchar(20),
username varchar(20),
coaudit int,
cograde int,
caddtime datetime
)insert @tb(coid,coname,username,coaudit,cograde,caddtime)
select convert(int,coid),coname,username,coaudit,cograde,caddtime
from company t
order by convert(char(10),caddtime,120) desc
,(select userpower from [user] where username=t.username) desc
where ID>'+convert(varchar,(@pageIndex-1)*@pagesize)
这样的话就要求按照生成的临时表的标识列应该是从小到大有顺序生成
可现在生成的标识列的数据是无序的,显示出来的数据不正确呀
CREATE TABLE #tb(ID bigint IDENTITY (1, 1) NOT NULL, Co_Name [nvarchar](100),User_Name [nvarchar](50),Co_Logo [nvarchar](50),Co_Products [nvarchar](100),Province_ID int)
INSERT #tb
(Co_Name,
User_Name,
Co_Logo,
Co_Products,
Province_ID
)
SELECT
Co_Name,
User_Name,
Co_Logo,
Co_Products,
Province_ID
FROM Opto_Company t ORDER BY CONVERT(char(10),
Co_Createtime,120) DESC,(SELECT User_Power FROM Opto_User WHERE
User_Name = t.User_Name) DESCdeclare @sql varchar(8000)
set @sql=' select top 10'
+' * from #tb where ID>'+convert(varchar,(@PageIndex-1)*@PageSize)exec(@sql)
drop table #tb