简单说就是根据一堆条件对某表筛选，对筛选后的数据执行两个查询：1是分页，2是对某个字段分组求和，现在筛选后的数据最多只有1W+

create proc [dbo].[pager_buyApply2]
(
@p_ouCode nvarchar(20) = null,
@p_applyNo varchar(20) = null,
@p_customerManagerName nvarchar(100) = null,
@p_customerName nvarchar(200) = null,
@p_oildepotCode varchar(20) = null,
@p_oilCode varchar(20) = null,
@p_approveStatus int = null,
@p_isTakeOver int = null,
@p_acceptUnit nvarchar(100) = null,
@p_startTime datetime = null,
@p_endTime datetime = null,
@p_recommender nvarchar(50) = null,
@p_canApplySearchInCompany int = 1,
@p_currentUserId varchar(100) = null,--
@p_noPage int = 0,
@pageSize int = 10,
@pageIndex int = 1,
@totalRowCount int out
)
as
begin

declare @isOildepot int;
select top 1 @isOildepot = iif(IsUnit = 4, 1, 0) from sys_orgunit where oucode=@p_ouCode

select request.* into #tmp_request from WF_Request request
where request.WorkflowCode in ('Buy', 'OneSettle', 'Promotion');

select apply.* into #tmp_VBuyAndDeliveryApply from VBuyAndDeliveryApply apply
where
(
    (OildepotCode=@p_ouCode and @isOildepot = 1)
    or (@isOildepot = 0 and ManagerUserId in
        (
        select UserId from SYS_OrgUser
        where
        (OUCode like dbo.func_GetOrgCodeByDepartment(@p_ouCode)  + '%' and @p_canApplySearchInCompany = 1)
        or (UserID = @p_currentUserId and @p_canApplySearchInCompany != 1)
        )
    )
)
and (@p_applyNo is null or len(@p_applyNo) = 0 or ApplyNo like '%' + @p_applyNo + '%')
and (@p_customerManagerName is null or len(@p_customerManagerName) = 0 or ManagerName like '%' + @p_customerManagerName + '%')
and (@p_customerName is null or len(@p_customerName) =0 or CustomerName like '%' + @p_customerName + '%')
and (@p_oildepotCode is null or len(@p_oildepotCode) = 0 or OildepotCode = @p_oildepotCode)
and (@p_oilCode is null or len(@p_oilCode) = 0 or OilCode = @p_oilCode)
and (@p_acceptUnit is null or len(@p_acceptUnit) = 0 or AcceptUnit like '%' + @p_acceptUnit + '%')
and (@p_isTakeOver is null or isnull(IsTakeOver, 0) = @p_isTakeOver)
and (@p_recommender is null or len(@p_recommender) = 0 or Recommender like '%' + @p_recommender + '%')
and (@p_startTime is null or CreatedDate >= @p_startTime)
and (@p_endTime is null or CreatedDate <= @p_endTime)

select apply.* into #tmp_fullPageData from #tmp_VBuyAndDeliveryApply apply
left join #tmp_request request on apply.ID = request.RequestID
where (@p_approveStatus is null or request.RequestStatus=@p_approveStatus);

select @totalRowCount = count(1) from #tmp_fullPageData;
if @p_noPage = 1
begin
    select * from #tmp_fullPageData;
end
else
begin
    declare @pageEnd int = @pageIndex * @pageSize;
    select * from (select ROW_NUMBER() over (order by CreatedDate desc) RowNum, * from #tmp_fullPageData) t
    where RowNum > (@pageEnd - @pageSize) and RowNum <= @pageEnd;
end

select OilName, SUM(OrderQty) 'Qty' from #tmp_fullPageData group by OilName;

drop table #tmp_request;
drop table #tmp_VBuyAndDeliveryApply;
drop table #tmp_fullPageData;
end