sqlserver 多表关联多条件分页排序查询-蒲公英云

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

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})