**SQL LEFT JOIN 分页查询笔记**

分页查询笔记

根据商品的地区分页显示20个订单，
订单表：类型 ，订单
商品表：类型，地区

错误：
SELECT TOP 20 c.* FROM
(select a.*,TypeID, row_number() over(ORDER BY OrderID DESC) as rownumber FROM tablea as a LEFT JOIN tableab as b ON a.AwardID = b.AwardID
) as c WHERE 1 = 1 AND TypeID = 1 AND rownumber> 0 ORDER BY OrderID DESC
分析：此时rownumber为tablea的原始表index,导致翻页的时候并非真实数据的第20.

正确：
SELECT TOP 20 d.* FROM
(SELECT , row_number() over(ORDER BY OrderID DESC) as rownumber FROM
(SELECT a.,TypeID FROM tablea as a LEFT JOIN tableb as b ON a.AwardID = b.AwardID WHERE 1 = 1 AND TypeID = 1) as c
) as d
WHERE rownumber> 20 ORDER BY OrderID DESC
分析：将表格先查找出来后命名为C，再将C提取。