使用聚合函数(partition by )的一个实例

SELECT id,bureauname,

       CASE WHEN NUMBERS = 'C' THEN schnumber END as aa,

       CASE WHEN NUMBERS = 'O' THEN schnumber END as bb,

       stunumber

       --count(schnumber)       

FROM  

  (select ID,BUREAUNAME,SCHNAME as SCHNUMBER,STATE as NUMBERS,COUNTSTU as STUNUMBER

    FROM

      (SELECT ID,BUREAUNAME,SCHID,SCHNAME,STATE,STUCOUNT,COUNTSTU

      FROM(SELECT P.PPFRAMEID AS SCHID,

                  S.NAME AS SCHNAME,

                  PPARAVALUE AS STATE,

                  COUNT(I.STUID)OVER(PARTITION BY NAME) AS STUCOUNT,

                  E.BUREAUNAME AS BUREAUNAME,

                  E.ID AS ID,

                  COUNT(I.STUID)OVER(PARTITION BY NAME) AS COUNTSTU

             FROM T_PRIVATEPARA P,T_SCHOOL_CODE S,T_STUDENT_INFO I,T_EDUCATION_BUREAU E 

             WHERE P.PPFRAMEID =S.CODEID 

             AND S.ADMINISTRATIVEAREA =E.ADMINISTRATIVEAREA

             AND P.PPFRAMEID =I.SCHID 

             AND STUSTATE = FSTUISREG(STUSTATE)

             AND P.PPARAID IN ('ZS')

             --AND P.PPARAVALUE IN ('O','C','B' )

             GROUP BY P.PPFRAMEID,S.NAME,P.PPARAVALUE,I.STUID,E.BUREAUNAME,E.ID)

          GROUP BY SCHID,SCHNAME,STATE,STUCOUNT,BUREAUNAME,ID,COUNTSTU)

       WHERE STATE IN ('C','O')

    GROUP BY ID ,BUREAUNAME,STATE,schname,countstu

    

  UNION

  

    select ID AS SS,BUREAUNAME AS RR,SCHNAME as SCHNUMBER1,STATE as NUMBERS,COUNTSTU as STUNUMBER

    FROM

      (SELECT ID,BUREAUNAME,SCHID,SCHNAME,STATE,STUCOUNT,COUNTSTU

      FROM(SELECT P.PPFRAMEID AS SCHID,

                  S.NAME AS SCHNAME,

                  PPARAVALUE AS STATE,

                  COUNT(I.STUID)OVER(PARTITION BY NAME) AS STUCOUNT,

                  E.BUREAUNAME AS BUREAUNAME,

                  E.ID AS ID,

                  COUNT(I.STUID)OVER(PARTITION BY NAME) AS COUNTSTU

             FROM T_PRIVATEPARA P,T_SCHOOL_CODE S,T_STUDENT_INFO I,T_EDUCATION_BUREAU E 

             WHERE P.PPFRAMEID =S.CODEID 

             AND S.ADMINISTRATIVEAREA =E.ADMINISTRATIVEAREA

             AND P.PPFRAMEID =I.SCHID 

             AND STUSTATE = FSTUISREG(STUSTATE)

             AND P.PPARAID IN ('ZS')

             --AND P.PPARAVALUE IN ('O','C','B' )

             GROUP BY P.PPFRAMEID,S.NAME,P.PPARAVALUE,I.STUID,E.BUREAUNAME,E.ID)

          GROUP BY SCHID,SCHNAME,STATE,STUCOUNT,BUREAUNAME,ID,COUNTSTU)

       WHERE STATE IN ('O')

    GROUP BY ID ,BUREAUNAME,STATE,schname,countstu)

GROUP BY NUMBERS,schnumber,id,bureauname,stunumber