ORACEl根据条件写sql

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118

select f1.NAME,        t1.year,        t1.remark,        t1.month,        f1.id_card,        f1.GWGZ,        f1.JSGZ,        f1.BLBT,        f1.TSJT,        f1.ZFBT,        f1.BFGZ,        f1.YFGZ,        f1.KYLBX,        f1.KZYNJ,        f1.KZFGJJ,        f1.KYILBX,        f1.KGRSDS,        f1.KKHJ,        f1.SFGZ,        f1.JWJT,        f1.SHBT,        f1.DQFJJTHJ,        f1.GGXBT,        f1.GWJTBT,        f1.HJ,        KZFGJJhj,        f1.seygwgz,        f1.seyjsgz,        (NVl(f1.gwgz,0) + f1.jsgz + f1.blbt + f1.tsjt + f1.KZFGJJhj + f1.jwjt +        f1.shbt + f1.ggxbt + f1.gwjtbt + f1.bfgz + f1.seygwgz + f1.seyjsgz -        f1.kylbx - f1.kzynj - f1.kyilbx - f1.kgrsds) heng   from (select rec_id,                t.NAME,                t.id_card,                sum(GWGZ) GWGZ,                sum(JSGZ) JSGZ,                sum(BLBT) BLBT,                sum(tsjt) TSJT,                sum(ZFBT) ZFBT,                sum(bfgz) BFGZ,                sum(YFGZ) YFGZ,                sum(KYLBX) KYLBX,                sum(KZYNJ) KZYNJ,                sum(KZFGJJ) KZFGJJ,                sum(KYILBX) KYILBX,                sum(KGRSDS) KGRSDS,                sum(KKHJ) KKHJ,                sum(SFGZ) SFGZ,                sum(JWJT) JWJT,                sum(SHBT) SHBT,                sum(DQFJJTHJ) DQFJJTHJ,                sum(GGXBT) GGXBT,                sum(GWJTBT) GWJTBT,                sum(HJ) HJ,                sum(KZFGJJhj) KZFGJJhj,                sum(seygwgz) seygwgz,                sum(seyjsgz) seyjsgz           from (select rec_id,                        NAME,                        id_card,                        GWGZ,                        JSGZ,                        BLBT,                        TSJT,                        ZFBT,                        BFGZ,                        YFGZ,                        KYLBX,                        KZYNJ,                        KZFGJJ,                        KYILBX,                        KGRSDS,                        KKHJ,                        SFGZ,                        JWJT,                        SHBT,                        DQFJJTHJ,                        GGXBT,                        GWJTBT,                        HJ,                        year,                        month,                        (case                          when month != '12' then                           KZFGJJ * 0                          else                           KZFGJJ * 12 * null                        end) KZFGJJhj,                        (case                          when month != '12' then                           GWGZ * 0                          else                           GWGZ                        end) seygwgz,                        (case                          when month != '12' then                           JSGZ * 0                          else                           JSGZ                        end) seyjsgz                   from T_WAGE_DATA                  where 1 = 1                    and NAME like '%--%'                    and to_char(enter_time, 'yyyy-MM') >= '2020-12'                    and to_char(enter_time, 'yyyy-MM') <= '2020-12'                    and data_valid = '1') t          group by rec_id, ID_CARD, NAME) f1   left join (select year, month, numbers, rec_id, remark                from T_WAGE_DATA t               where 1 = 1                 and NAME like '%--%'                 and to_char(enter_time, 'yyyy-MM') >='2020-12'                 and to_char(enter_time, 'yyyy-MM') <= '2020-12'                 and data_valid = '1') t1     on t1.rec_id = f1.rec_id  order by to_number(year) desc,           to_number(month) desc,           to_number(numbers) asc