row_number() over 两表连接分页SQL

浏览数：20 / 时间：2015年06月12日

--总条数
SELECT COUNT(1)
FROM  dbo.osaleh   INNER JOIN dbo.osaled 
ON dbo.osaleh.osaleh_osalehID=dbo.osaled.osaled_osalehID WHERE 
--两表连接分页
SELECT * FROM(
	SELECT ROW_NUMBER() OVER ( ORDER BY osaleh_osalehID DESC ) AS rownum,
	dbo.osaleh.osaleh_osalehID,
	dbo.osaleh.osaleh_custname,
	dbo.osaleh.osaleh_tel,
	dbo.osaleh.osaleh_Name,
	dbo.osaleh.osaleh_userID,
	(SELECT User_LastName FROM dbo.Users (NOLOCK) WHERE User_UserId=dbo.osaleh.osaleh_userID) AS SalesManName,
	dbo.osaleh.osaleh_mkdate,
	dbo.osaled.osaled_oactvhID
	FROM  dbo.osaleh   INNER JOIN dbo.osaled   
	ON dbo.osaleh.osaleh_osalehID=dbo.osaled.osaled_osalehID
	WHERE dbo.osaled.osaled_oactvhID=2
) AS tb WHERE rownum BETWEEN( (1 - 1) *5+1) AND ( 1 * 5 )

--如果不排序
SELECT * FROM(
	SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT 0) ) AS rownum,
	dbo.osaleh.osaleh_osalehID,
	dbo.osaleh.osaleh_custname,
	dbo.osaleh.osaleh_tel,
	dbo.osaleh.osaleh_Name,
	dbo.osaleh.osaleh_userID,
	(SELECT User_LastName FROM dbo.Users (NOLOCK) WHERE User_UserId=dbo.osaleh.osaleh_userID) AS SalesManName,
	dbo.osaleh.osaleh_mkdate,
	dbo.osaled.osaled_oactvhID
	FROM  dbo.osaleh   INNER JOIN dbo.osaled   
	ON dbo.osaleh.osaleh_osalehID=dbo.osaled.osaled_osalehID
	WHERE dbo.osaled.osaled_oactvhID=2
) AS tb WHERE rownum BETWEEN( (1 - 1) *5+1) AND ( 1 * 5 )

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。