处理一对多的关系，比如一个订单 里面 有n 个商品

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

alter PROCEDURE [dbo].[sp_OrderPagination]
(
 @fields varchar(1000) = '*',        -- 需要返回的列
 @sortField varchar(255)='',         -- 排序的字段名, max(字段)或min(字段)字段不重复
 @pageSize   int = 10,               -- 页尺寸
 @pageIndex  int = 1,                -- 页码
 @sortDescending bit = 0,   -- 是否降序
 @strWhere  varchar(1500) = '',  -- 查询条件 (注意: 不要加 WHERE)
 @totalRecords int=0 output   -- 总记录数
)
AS
SET NOCOUNT ON;
DECLARE @tblName  varchar(4000)    -- 定单
DECLARE @strSQL   nvarchar(4000)   -- 主语句
DECLARE @strOrder varchar(400)    -- 排序类型
DECLARE @firstRow int      -- 定义第一行记录
DECLARE @lastRow int      -- 定义最后一条记录
SET @firstRow=((@pageIndex-1)*@pageSize)+1 -- 设置第一条记录
SET @lastRow=@firstRow+@pageSize   -- 设置最后一条记录
SET @tblName='SELECT o.OrderId,o.Ordernum,o.Username,o.UserId,o.Price,o.IsPayment,o.IsPay,o.PayType,o.PayTime,o.Status,o.ShipStatus,o.Postage,o.PostType,o.ServiceCost,o.Memo,o.[Time],oi.LineNum,oi.SupplierId,oi.SupplierTitle,oi.ProductId,oi.ProductName,oi.Descn,oi.Thumb,oi.UnitPrice,oi.Quantity,oi.DiscountRate,o.Hidden1,o.Hidden2 into #ttemp FROM db_Order o join db_OrderItem oi on o.Ordernum=oi.Ordernum '

    IF @strWhere !=''
  SET @strSQL = @tblName+' SELECT @cnt=count(1) FROM #ttemp WHERE LineNum=1 AND '+@strWhere
    ELSE
  SET @strSQL = @tblName+'SELECT @cnt=count(1) FROM #ttemp WHERE LineNum=1'
  --EXEC sp_executesql @strSQL,N'@cnt int output',@totalRecords output

 IF @sortDescending != 0
  SET @strOrder = ' ORDER BY ' + @sortField +' desc,LineNum asc'
 ELSE
  SET @strOrder = ' ORDER BY ' + @sortField +' asc,LineNum asc' 

  --以下代码赋予了@strSQL以真正执行的SQL代码 

 IF @strWhere != ''
 BEGIN
 SET @strSQL = @strSQL+ 'SELECT '+@fields+',RowNo,COUNT(RowNo) OVER(PARTITION BY RowNo) CountItem FROM'
 +'(SELECT *,DENSE_RANK() OVER (ORDER BY TB.OrderId DESC) AS RowNo FROM #ttemp as tb WHERE ' + @strWhere + ' ) AS T '
 +'WHERE RowNo >='+cast(@firstRow as nvarchar(10))+' AND RowNo <'+cast(@lastRow as nvarchar(10))
 END
 else
  BEGIN
   SET @strSQL =@strSQL+ 'SELECT '+@fields+',RowNo,COUNT(RowNo) OVER(PARTITION BY RowNo) CountItem FROM'
            +'(SELECT *,DENSE_RANK() OVER (ORDER BY TB.OrderId DESC) AS RowNo FROM #ttemp as tb) AS T '
   +'WHERE RowNo >='+cast(@firstRow as nvarchar(10))+' AND RowNo <'+cast(@lastRow as nvarchar(10))
  END
SET @strSQL= @strSQL+@strOrder
EXEC sp_executesql @strSQL,N'@cnt int output',@totalRecords output
SET NOCOUNT OFF;