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语句看起来比较清晰。