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 

请问这段存储过程里 目前是IU_REGDATE 来排序的 我想用 别名 TOTAL 来排序的话怎么做呢？ ROW_NUMBER() 不能用别名来排序 想了半天想不出办法来。。 请高手指点迷津 sql2005 存储 分页 排序
[解决办法]
--把TOP 100 PERCENT去掉,加上没意义
--把CTE换成子查询
如果还不对，仔细分析你的逻辑