初学者高手帮忙看下

2012-05-11

菜鸟求助高手帮忙看下SQL codeSELECT *,ROW_NUMBER() OVER(ORDER BY tba.stuno DESC) AS ROWNUMBER FROMW

菜鸟求助高手帮忙看下

SQL code

        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

为什么执行不通高手帮忙看下谢谢了

[解决办法]
表表达式的用法是这样的

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                                     ) ,                            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),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            )--查询语句必须是在with ...as 之后  SELECT *,ROW_NUMBER() OVER(ORDER BY tba.stuno DESC) AS ROWNUMBER FROM             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

热点排行

Bad Request.

SQL Server

初学者 高手帮忙看下

初学者高手帮忙看下