SQL分页查询+Inner Join

使用select top not in和inner join语句，因为一条记录（房源）可能对应多条记录（多个用户），所以出现查询结果重复的现象。

原查询语句如下：

Select top perPage From Table1 Inner Join Table2 On ... Where ... And ID not in (select top perPage * (page - 1) ID From Table1 Inner Join Table2 On ... Where ... Order By ...) Order By ...

Group By 添加在Where后面无效

改为：

Select ... From Table1 Where ID in(Select top perPage ID From Table1 Inner Join Table2 On ... Where ... And ID not in (select top perPage * (page - 1) ID From Table1 Inner Join Table2 On ... Where ... Group By ID Order By ...) Group By ID Order By ...

也可以使用临时表或rownumber, sql语句看起来比较清晰。

转载于:https://www.cnblogs.com/xiufei/archive/2011/02/28/1961819.html