orc_select.txt

oracle 多联合复杂的SQL语句

SELECT a.dwnm, b.ckrs, a.rs, a.zs, a.zb, a.wt, b.ssnjm minssnjm,
       a.ssnjm maxssnjm
  FROM (SELECT   dwnm, COUNT (xh) rs, SUM (NVL (zs, 0)) zs,
                 SUM (NVL (zb, 0)) zb, SUM (NVL (wt, 0)) wt, MAX (ssnjm)
                                                                        ssnjm
            FROM (SELECT xh, dwnm, ssnjm,
                         CASE
                            WHEN flfxm = '1' OR flfxm = '2'
                               THEN 1
                         END zs, CASE
                            WHEN flfxm = '6'
                               THEN 1
                         END zb, CASE
                            WHEN flfxm = '5'
                               THEN 1
                         END wt
                    FROM xs_bks_xjb
                   WHERE ssnjm =
                            (SELECT maxssnjm
                               FROM ((SELECT CASE
                                                WHEN hs = 2
                                                     OR hs = 1
                                                   THEN ssnjm
                                                ELSE 'YYYYY'
                                             END maxssnjm,
                                             CASE
                                                WHEN hs = 2
                                                   THEN minssnjm
                                                ELSE 'YYYYY'
                                             END minssnjm
                                        FROM (SELECT MAX (hs) hs,
                                                     MAX (ssnjm) ssnjm,
                                                     MIN (ssnjm) minssnjm
                                                FROM (SELECT ssnjm, ROWNUM hs
                                                        FROM (SELECT   ssnjm
                                                                  FROM xs_bks_xjb
                                                              GROUP BY ssnjm)))))))
        GROUP BY dwnm) a,
       (SELECT   dwnm, COUNT (xh) ckrs, MIN (ssnjm) ssnjm
            FROM xs_bks_xjb
           WHERE ssnjm =
                    (SELECT minssnjm
                       FROM ((SELECT CASE
                                        WHEN hs = 2
                                           THEN ssnjm
                                        ELSE 'YYYYY'
                                     END maxssnjm,
                                     CASE
                                        WHEN hs = 2
                                           THEN minssnjm
                                        ELSE 'YYYYY'
                                     END minssnjm
                                FROM (SELECT MAX (hs) hs, MAX (ssnjm) ssnjm,
                                             MIN (ssnjm) minssnjm
                                        FROM (SELECT ssnjm, ROWNUM hs
                                                FROM (SELECT   ssnjm
                                                          FROM xs_bks_xjb
                                                      GROUP BY ssnjm))))))
        GROUP BY dwnm) b
 WHERE a.dwnm = b.dwnm(+)
union
SELECT a.dwnm, b.ckrs, a.rs, a.zs, a.zb, a.wt, b.ssnjm minssnjm,
       a.ssnjm maxssnjm
  FROM (SELECT   dwnm, COUNT (xh) rs, SUM (NVL (zs, 0)) zs,
                 SUM (NVL (zb, 0)) zb, SUM (NVL (wt, 0)) wt, MAX (ssnjm)
                                                                        ssnjm
            FROM (SELECT xh, dwnm, ssnjm,
                         CASE
                            WHEN flfxm = '1' OR flfxm = '2'
                               THEN 1
                         END zs, CASE
                            WHEN flfxm = '6'
                               THEN 1
                         END zb, CASE
                            WHEN flfxm = '5'
                               THEN 1
                         END wt
                    FROM xs_bks_xjb
                   WHERE ssnjm =
                            (SELECT maxssnjm
                               FROM ((SELECT CASE
                                                WHEN hs = 2
                                                     OR hs = 1
                                                   THEN ssnjm
                                                ELSE 'YYYYY'
                                             END maxssnjm,
                                             CASE
                                                WHEN hs = 2
                                                   THEN minssnjm
                                                ELSE 'YYYYY'
                                             END minssnjm
                                        FROM (SELECT MAX (hs) hs,
                                                     MAX (ssnjm) ssnjm,
                                                     MIN (ssnjm) minssnjm
                                                FROM (SELECT ssnjm, ROWNUM hs
                                                        FROM (SELECT   ssnjm
                                                                  FROM xs_bks_xjb
                                                              GROUP BY ssnjm)))))))
        GROUP BY dwnm) a,
       (SELECT   dwnm, COUNT (xh) ckrs, MIN (ssnjm) ssnjm
            FROM xs_bks_xjb
           WHERE ssnjm =
                    (SELECT minssnjm
                       FROM ((SELECT CASE
                                        WHEN hs = 2
                                           THEN ssnjm
                                        ELSE 'YYYYY'
                                     END maxssnjm,
                                     CASE
                                        WHEN hs = 2
                                           THEN minssnjm
                                        ELSE 'YYYYY'
                                     END minssnjm
                                FROM (SELECT MAX (hs) hs, MAX (ssnjm) ssnjm,
                                             MIN (ssnjm) minssnjm
                                        FROM (SELECT ssnjm, ROWNUM hs
                                                FROM (SELECT   ssnjm
                                                          FROM xs_bks_xjb
                                                      GROUP BY ssnjm))))))
        GROUP BY dwnm) b
 WHERE b.dwnm =a.dwnm(+)
 union
SELECT CASE
          WHEN hs > 2
             THEN   '不能统计'
       END dwnm, 0 ckrs, 0 rs, 0 zs, 0 zb, 0 wt,
       CASE
          WHEN hs > 2
             THEN   '年级数' || TO_CHAR (hs) || '个' END  minssnjm,
      CASE
          WHEN hs > 2
             THEN    '年级数' || TO_CHAR (hs) || '个' END maxssnjm
  FROM (SELECT MAX (hs) hs, MAX (ssnjm) ssnjm, MIN (ssnjm) minssnjm
          FROM (SELECT ssnjm, ROWNUM hs
                  FROM (SELECT   ssnjm
                            FROM xs_bks_xjb
                        GROUP BY ssnjm)))