row_number() over 兩表串連分頁SQL

標籤：row_number over   兩表串連分頁   row_number over 不排序   

--總條數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_oactvhIDFROM  dbo.osaleh   INNER JOIN dbo.osaled   ON dbo.osaleh.osaleh_osalehID=dbo.osaled.osaled_osalehIDWHERE 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_oactvhIDFROM  dbo.osaleh   INNER JOIN dbo.osaled   ON dbo.osaleh.osaleh_osalehID=dbo.osaled.osaled_osalehIDWHERE dbo.osaled.osaled_oactvhID=2) AS tb WHERE rownum BETWEEN( (1 - 1) *5+1) AND ( 1 * 5 )

row_number() over 兩表串連分頁SQL