SELECT *,ROW_NUMBER() OVER(ORDER BY tba.stuno DESC) AS ROWNUMBER FROM 
            WITH #tbx AS
            (    
                SELECT *,ROW_NUMBER() OVER(ORDER BY tba.stuno DESC) AS ROWNUMBER FROM
                (SELECT stuno , factamount=ISNULL(SUM(factamount),'0') 
                FROM View_ClassRpt_Query 
                WHERE classNo=1 AND xibu='软件工程' GROUP BY stuno) 
                tba JOIN 
                (SELECT stuno AS 'stunob',stuname,xibu,banji,amount=(SELECT amount FROM T_PayCS WHERE classNo=1),stayamount=(SELECT stayamount FROM T_PayCS WHERE classNo=1),isstay 
                FROM View_ClassRpt_Query 
                WHERE classNo=1  AND xibu='软件工程' GROUP BY stuno,stuname,xibu,banji,amount,stayamount,isstay)
                tbb  on tba.stuno=tbb.stunob
            )
            join (select stuno as 'stunoc', qianfeijine= CASE isstay WHEN 1 THEN stayamount-factamount WHEN 0 THEN amount-factamount END  
            from #tbx where classNo=1  AND xibu='软件工程') tbc
            on #tbx.stuno=tbc.stunoc

 WITH #tbx 
 AS
(    
    SELECT  * ,
            ROW_NUMBER() OVER ( ORDER BY tba.stuno DESC ) AS ROWNUMBER
    FROM    ( SELECT    stuno ,
                        factamount = ISNULL(SUM(factamount), '0')
              FROM      View_ClassRpt_Query
              WHERE     classNo = 1
                        AND xibu = '软件工程'
              GROUP BY  stuno
            ) tba
            JOIN ( SELECT   stuno AS 'stunob' ,
                            stuname ,
                            xibu ,
                            banji ,
                            amount = ( SELECT   amount
                                       FROM     T_PayCS
                                       WHERE    classNo = 1
                                     ) ,
                            stayamount = ( SELECT   stayamount
                                           FROM     T_PayCS
                                           WHERE    classNo = 1
                                         ) ,
                            isstay
                   FROM     View_ClassRpt_Query
                   WHERE    classNo = 1
                            AND xibu = '软件工程'
                   GROUP BY stuno ,
                            stuname ,
                            xibu ,
                            banji ,
                            amount ,
                            stayamount ,
                            isstay
                 ) tbb ON tba.stuno = tbb.stunob
)

 
  SELECT    * ,
            ROW_NUMBER() OVER ( ORDER BY tba.stuno DESC ) AS ROWNUMBER
  FROM      #tbx
            JOIN ( SELECT   stuno AS 'stunoc' ,
                            qianfeijine = CASE isstay
                                            WHEN 1
                                            THEN stayamount - factamount
                                            WHEN 0 THEN amount - factamount
                                          END
                   FROM     #tbx
                   WHERE    classNo = 1
                            AND xibu = '软件工程'
                 ) tbc ON #tbx.stuno = tbc.stunoc

------解决方案--------------------

SQL code


            WITH #tbx AS
            (    
                SELECT *,ROW_NUMBER() OVER(ORDER BY tba.stuno DESC) AS ROWNUMBER FROM
                (SELECT stuno , factamount=ISNULL(SUM(factamount),'0') 
                FROM View_ClassRpt_Query 
                WHERE classNo=1 AND xibu='软件工程' GROUP BY stuno) 
                tba JOIN 
                (SELECT stuno AS 'stunob',stuname,xibu,banji,amount=(SELECT amount FROM T_PayCS WHERE classNo=1),stayamoun