sqlserver 多表关联多条件分页排序查询

最新推荐文章于 2024-07-25 18:23:42 发布

wcy10086

最新推荐文章于 2024-07-25 18:23:42 发布

阅读量2.3k

点赞数

文章标签： sql sql server 分页 select

本文链接：https://blog.csdn.net/qq_36949713/article/details/75000526

版权

SELECT TOP
		(#{limit}) 
		storeId,
		intermediaryId,
		storeAddress,
		storeName,
		storePhone,
		principal,
		principalPhone,
		isEnable
		FROM
		(
		SELECT ROW_NUMBER() OVER (ORDER BY
		b.intermediaryId)
		AS RowNumber,
		storeId,
		intermediaryId,
		storeAddress,
		storeName,
		storePhone,
		principal,
		principalPhone,
		isEnable 
		FROM
		(select 
		uis.storeId,
		uis.intermediaryId,
		uis.storeAddress,
		uis.storePhone,
		uis.storeName,
		uis.principal,
		uis.principalPhone,
		uis.isEnable from user_inter_store uis inner join user_intermediary  ui on uis.intermediaryId = ui.intermediaryId  where uis.isEnable=1
		
		<if test=" name !=null and name !='' ">
        and ui.name = #{name}
        </if>
          <if test=" principal !=null and principal !='' ">
          and uis.principal =#{principal}
        </if>
        <if test=" storeAddress !=null and storeAddress !='' ">
        and uis.storeAddress  =#{storeAddress}
        </if>
        <if test=" storeName !=null and storeName !='' ">
        and uis.storeName  =#{storeName}
        </if>
        <if test=" principalPhone !=null and principalPhone !='' ">
        and uis.principalPhone like =#{principalPhone}
        </if>
        <if test="sort ==-1  ">
          order by uis.storeId desc 
        </if>
        
		)
     as b
		) as a
		WHERE (a.RowNumber >
		#{pages})