sql2005 存储过程 ROW_NUMBER 排序问题
先附上源码
USE [cool]
GO
/****** 对象: StoredProcedure [dbo].[UP_ieveInfo_User] 脚本日期: 08/12/2013 14:41:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UP_ieveInfo_User]
@page INT
, @pageSize INT
, @keywordType NVARCHAR(20) = ''
, @keyword NVARCHAR(4000) = ''
, @search2 NVARCHAR(4000) = ''
, @search4 NVARCHAR(4000) = ''
, @search5 NVARCHAR(4000) = ''
, @search6 NVARCHAR(4000) = ''
, @search7 NVARCHAR(4000) = ''
, @startPeriod NVARCHAR(20) = ''
, @endPeriod NVARCHAR(20) = ''
, @site NVARCHAR(20) = ''
AS
SET NOCOUNT ON ;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @filter NVARCHAR(MAX);
SET @filter = '';
SET @page = ISNULL(@page, 1);
SET @pageSize = ISNULL(@pageSize, 10);
SET @SQL = '
WITH ALLROWS AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY IU_REGDATE desc) AS ROWNUMBER
, IU_IDX
, IU_ID
, IU_SITE
, IU_NICKNAME
, IU_CASH
, ((SELECT isNull(SUM(IC_AMOUNT),0) FROM INFO_CHARGE WHERE ALLROWS.IU_ID = IC_ID and ic_status = 1) - (SELECT SUM(IE_AMOUNT) FROM INFO_EXCHANGE WHERE ALLROWS.IU_ID = IE_ID and ie_status = 1) - IU_CASH) AS TOTAL
FROM
dbo.Info_User a
WITH(NOLOCK)
WHERE
1=1
AND IU_ID IN (SELECT distinct(IE_ID) FROM INFO_EXCHANGE where ie_status = ''1'')
'
IF @keyword <> '' AND @keywordType <> ''
BEGIN
SET @filter = ' AND ' + @keywordType + ' LIKE ''%'' + @keyword + ''%'' '
END
IF @startPeriod <> ''
BEGIN
SET @filter = @filter + ' AND IU_REGDATE >= @startPeriod '
END
IF @endPeriod <> ''
BEGIN
SET @filter = @filter + ' AND IU_REGDATE <= @endPeriod '
END
IF @site <> 'all'
BEGIN
SET @filter = @filter + ' AND IU_SITE = @site '
END
SET @SQL = @SQL + @filter +
'
)
SELECT
ROWNUMBER
, IU_IDX
, IU_ID
, IU_SITE
, IU_NICKNAME
, IU_CASH
, (SELECT isNull(SUM(IC_AMOUNT),0) FROM INFO_CHARGE WHERE ALLROWS.IU_ID = IC_ID and ic_status = 1) AS CNT1
, (SELECT isNull(SUM(IE_AMOUNT),0) FROM INFO_EXCHANGE WHERE ALLROWS.IU_ID = IE_ID and ie_status = 1) AS CNT2
, (((SELECT isNull(SUM(IE_AMOUNT),0) FROM INFO_EXCHANGE WHERE ALLROWS.IU_ID = IE_ID and ie_status = 1) + IU_CASH) / (SELECT SUM(IC_AMOUNT) FROM INFO_CHARGE WHERE ALLROWS.IU_ID = IC_ID and ic_status = 1) * 100) AS PER
, (SELECT MAX(ROWNUMBER) TC FROM ALLROWS) AS TC
FROM
ALLROWS
WHERE
ROWNUMBER
BETWEEN
(@page - 1) * @pageSize + 1
AND
@page * @pageSize;
';
EXECUTE sp_executesql
@SQL,
N'@page INT, @pageSize INT, @keyword NVARCHAR(4000),
@startPeriod NVARCHAR(20), @endPeriod NVARCHAR(20), @site NVARCHAR(20)',
@page = @page, @pageSize = @pageSize, @keyword = @keyword,
@startPeriod=@startPeriod, @endPeriod=@endPeriod, @site=@site
