row_number() over 两表连接分页SQL 一对多的表关系 去重

--总条数
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 )


--两表连接分页 一对多的表关系  去重
SELECT  * FROM(
SELECT
ROW_NUMBER() OVER ( ORDER BY osaleh_mkdate DESC ) AS rownum,*
FROM
(
select DISTINCT
 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.osaleh.osaleh_Status
FROM  dbo.osaleh   INNER JOIN dbo.osaled   
ON dbo.osaleh.osaleh_osalehID=dbo.osaled.osaled_osalehID
WHERE  1=1 and dbo.osaleh.osaleh_type='01' and (dbo.osaleh.osaleh_Deleted IS NULL OR dbo.osaleh.osaleh_Deleted=0 )
 ) AS tb
)
tc WHERE 
 rownum BETWEEN(0*5+1) AND ( 5 )

******************************************别墅图纸推荐*************************************************

我想有一栋别墅，面朝大海，春暖花开

龙兴科技别墅图纸设计，这里有最新最全的别墅图纸，

这里有最给力的别墅图纸折扣活动，图纸包括建筑图、结构图、给排水图、电气图

我们致力于为广大客户提供别墅设计图纸,图纸均由专业、经验丰富的设计团队设计，可免费提供施工技术指导

地址：http://bstzsj.taobao.com/