在项目开发中对数据分时统计的一个SQL

/* Formatted on 2008/10/24 08:23 (Formatter Plus v4.8.8) */
SELECT   t2.userid userid, COUNT (t2.rono) AS totalnum,
         SUM (t2.pass_num7_9) a1pass, SUM (ng_num7_9) a1ng,
         SUM (pass_num9_11) a2pass, SUM (ng_num9_11) a2ng,
         SUM (pass_num11_14) a3pass, SUM (ng_num11_14) a3ng,
         SUM (pass_num14_16) a4pass, SUM (ng_num14_16) a4ng,
         SUM (pass_num16_17) a5pass, SUM (ng_num16_17) a5ng,
         SUM (pass_num17_19) a6pass, SUM (ng_num17_19) a6ng,
         SUM (pass_num19_22) a7pass, SUM (ng_num19_22) a7ng,
         SUM (pass_num22_24) a8pass, SUM (ng_num22_24) a8ng,
         SUM (pass_num0_3) a9pass, SUM (ng_num0_3) a9ng,
         SUM (pass_num3_6) a10pass, SUM (ng_num3_6) a10ng,
         SUM (pass_num6_7) a11pass, SUM (ng_num6_7) a11ng
    FROM (SELECT userid, rono,
                   DECODE (SIGN (hours - 7),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (9 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num7_9,
                   DECODE (SIGN (hours - 7), 1, 1, 0, 1, -1, 0)
                 * DECODE (SIGN (9 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num7_9,
                   DECODE (SIGN (hours - 9),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (11 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num9_11,
                   DECODE (SIGN (hours - 9), 1, 1, 0, 1, -1, 0)
                 * DECODE (SIGN (11 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num9_11,
                   DECODE (SIGN (hours - 11),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (14 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num11_14,
                   DECODE (SIGN (hours - 11),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (14 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num11_14,
                   DECODE (SIGN (hours - 14),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (16 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num14_16,
                   DECODE (SIGN (hours - 14),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (16 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num14_16,
                   DECODE (SIGN (hours - 16),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (17 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num16_17,
                   DECODE (SIGN (hours - 16),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (17 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num16_17,
                   DECODE (SIGN (hours - 17),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (19 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num17_19,
                   DECODE (SIGN (hours - 17),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (19 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num17_19,
                   DECODE (SIGN (hours - 19),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (22 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num19_22,
                   DECODE (SIGN (hours - 19),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (22 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num19_22,
                   DECODE (SIGN (hours - 22),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (24 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num22_24,
                   DECODE (SIGN (hours - 22),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (24 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num22_24,
                   DECODE (SIGN (hours - 0),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (3 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num0_3,
                   DECODE (SIGN (hours - 0), 1, 1, 0, 1, -1, 0)
                 * DECODE (SIGN (3 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num0_3,
                   DECODE (SIGN (hours - 3),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (6 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num3_6,
                   DECODE (SIGN (hours - 3), 1, 1, 0, 1, -1, 0)
                 * DECODE (SIGN (6 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num3_6,
                   DECODE (SIGN (hours - 6),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (7 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num6_7,
                   DECODE (SIGN (hours - 6), 1, 1, 0, 1, -1, 0)
                 * DECODE (SIGN (7 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num6_7
            FROM (SELECT userid, rono, seqno, station, enddt, RESULT,
                         TO_NUMBER (TO_CHAR (enddt, 'HH24'), '99') AS hours
                    FROM ertbroflwd t1
                   WHERE station = :station
                     AND enddt >= bs_to_timestamp_tz (:startdt)
                     AND enddt <= bs_to_timestamp_tz (:enddt)
                     AND (RESULT = 'PASS' OR RESULT = 'NG')
                     AND seqno =
                            (SELECT MAX (seqno) seqno
                               FROM ertbroflwd t0
                              WHERE t1.rono = t0.rono
                                AND t1.station = t0.station
                                AND t0.enddt >= bs_to_timestamp_tz (:startdt)
                                AND t0.enddt <= bs_to_timestamp_tz (:enddt)
                                AND (RESULT = 'PASS' OR RESULT = 'NG')))) t2
GROUP BY t2.userid
ORDER BY t2.userid