Create Proc [dbo].[Sp_Po_Search] @FirstDate datetime, @EndDate Datetime, @pageIndex int, @pageSize int, @Counts int output as DECLARE @startRow int, @endRow int Set @startRow = (@pageIndex - 1) * @pageSize +1 SET @endRow = @startRow + @pageSize -1
IF OBJECT_ID('Tempdb..#TblPo') IS NOT NULL DROP TABLE #TblPo SELECT a.PoNo,a.PoDate INTO #TblPo From TblPo a Where Convert(char(10),a.PoDate,120) Between isnull(@firstdate,Convert(char(10),a.podate,120)) and isnull(@enddate,convert(char(10),a.podate,120)) SET @Counts=@@rowcount CREATE INDEX IX_#TblPo ON #TblPo(PoDate,PoNo)Select * From ( Select a.PoNo, Convert(char(10),a.PoDate,120) as PoDate, c.Supplier, ROW_NUMBER() OVER (ORDER BY a.PoDate DESC) AS RowNumber From #TblPo a Left Join TblPoList b On a.PoNo=b.PoNo Left Join TblSupplier c on a.SupplierNo=c.SupplierNo Group by a.PoNo,a.PoDate,c.Supplier ) t WHERE t.RowNumber BETWEEN @startRow AND @endRow 这样改测测性能
既然用了row_number()函数就应该利用cte嘛;with cte as( Select a.PoNo, Convert(char(10),a.PoDate,120) as PoDate, c.Supplier, ROW_NUMBER() OVER (ORDER BY a.PoDate DESC) AS RowNumberFrom TblPo a Left Join TblPoList b On a.PoNo=b.PoNo Left Join TblSupplier c on a.SupplierNo=c.SupplierNo Where a.PoDate Between isnull(@firstdate,Convert(char(10),a.podate,120)) and isnull(@enddate,convert(char(10),a.podate,120)) Group by a.PoNo,a.PoDate,c.Supplier ) select a.*,b.ct from cte a,(select count(1)as ct from cte)b WHERE RowNumber BETWEEN @startRow AND @endRow多加了一列,作为全部数据数量的输出
@FirstDate datetime,
@EndDate Datetime,
@pageIndex int,
@pageSize int,
@Counts int output
as
DECLARE @startRow int, @endRow int
Set @startRow = (@pageIndex - 1) * @pageSize +1
SET @endRow = @startRow + @pageSize -1
IF OBJECT_ID('Tempdb..#TblPo') IS NOT NULL
DROP TABLE #TblPo
SELECT a.PoNo,a.PoDate INTO #TblPo From TblPo a Where Convert(char(10),a.PoDate,120) Between isnull(@firstdate,Convert(char(10),a.podate,120)) and isnull(@enddate,convert(char(10),a.podate,120))
SET @Counts=@@rowcount
CREATE INDEX IX_#TblPo ON #TblPo(PoDate,PoNo)Select * From (
Select
a.PoNo,
Convert(char(10),a.PoDate,120) as PoDate,
c.Supplier,
ROW_NUMBER() OVER (ORDER BY a.PoDate DESC) AS RowNumber
From #TblPo a
Left Join TblPoList b On a.PoNo=b.PoNo
Left Join TblSupplier c on a.SupplierNo=c.SupplierNo
Group by a.PoNo,a.PoDate,c.Supplier
) t
WHERE t.RowNumber BETWEEN @startRow AND @endRow
这样改测测性能
Select
a.PoNo,
Convert(char(10),a.PoDate,120) as PoDate,
c.Supplier,
ROW_NUMBER() OVER (ORDER BY a.PoDate DESC) AS RowNumberFrom TblPo a
Left Join TblPoList b On a.PoNo=b.PoNo
Left Join TblSupplier c on a.SupplierNo=c.SupplierNo
Where a.PoDate Between isnull(@firstdate,Convert(char(10),a.podate,120)) and isnull(@enddate,convert(char(10),a.podate,120))
Group by a.PoNo,a.PoDate,c.Supplier
) select a.*,b.ct from cte a,(select count(1)as ct from cte)b
WHERE RowNumber BETWEEN @startRow AND @endRow多加了一列,作为全部数据数量的输出