比較簡單的查詢結果
SELECT hr110_factory_no,
       SUM(CASE
             WHEN hr110_in_date > ADD_MONTHS(SYSDATE, -3) THEN
             1
             ELSE
              0
           END) mans_3,
       SUM(CASE
             WHEN hr110_in_date <= ADD_MONTHS(SYSDATE, -3) and
                  hr110_in_date > ADD_MONTHS(SYSDATE, -6) THEN
              1
             ELSE
              0
           END) mans_3_6,
       SUM(CASE
             WHEN hr110_in_date <= ADD_MONTHS(SYSDATE, -6) and
                  hr110_in_date > ADD_MONTHS(SYSDATE, -12) THEN
              1
             ELSE
              0
           END) mans_6_12,
       SUM(CASE
             WHEN hr110_in_date <= ADD_MONTHS(SYSDATE, -12) and
                  hr110_in_date > ADD_MONTHS(SYSDATE, -24) THEN
              1
             ELSE
              0
           END) mans_12_24,
       SUM(CASE
             WHEN hr110_in_date <= ADD_MONTHS(SYSDATE, -24) and
                  hr110_in_date > ADD_MONTHS(SYSDATE, -36) THEN
              1
             ELSE
              0
           END) mans_24_36,
       SUM(CASE
             WHEN hr110_in_date <= ADD_MONTHS(SYSDATE, -36) THEN
              1
             ELSE
              0
           END) mans_36
FROM hr110
WHERE hr110_company_no = 'GSM'
   AND hr110_out_date is null
   --AND NVL2(hr110_out_date, TO_CHAR(hr110_out_date, 'yyyymmdd'), '19000101') = '19000101'
GROUP BY hr110_factory_no
   
------第二種方式
Select a.af_factory_no "廠區",
       a.af_total + a.am_total "3月以內",
       b.bf_total + b.bm_total "3-6個月",
    c.cf_total + c.cm_total "6-12個月",
    d.df_total + d.dm_total "1-2年",
    e.ef_total + e.em_total "2-3年",
    f.ff_total + f.fm_total "3年以上",
       a.af_total + b.bf_total + c.cf_total + d.df_total + e.ef_total + f.ff_total "男",
    a.am_total + b.bf_total + c.cf_total + d.df_total + e.ef_total + f.ff_total "女"
From (Select   af.factory_no af_factory_no, SUM (af.worker_no) af_total, af.sex af_sex, SUM (am.worker_no) am_total, am.sex am_sex
            From (Select   hr110_factory_no factory_no, COUNT (hr110_worker_no) worker_no, hr110_sex sex
                      From hr110
                     Where hr110_company_no = 'GSM'
                       And hr110_sex = 'F'
                       And hr110_out_date Is Null
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) = 3
                  Group By hr110_factory_no, hr110_sex) af,
                 (Select   hr110_factory_no factory_no, COUNT (hr110_worker_no) worker_no, hr110_sex sex
                      From hr110
                     Where hr110_company_no = 'GSM'
                       And hr110_sex = 'M'
                       And hr110_out_date Is Null
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) = 3
                  Group By hr110_factory_no, hr110_sex) am
           Where af.factory_no = am.factory_no
        Group By af.factory_no, af.sex, am.sex) a,
       (Select   bf.factory_no bf_factory_no, SUM (bf.worker_no) bf_total, bf.sex bf_sex, SUM (bm.worker_no) bm_total, bm.sex bm_sex
            From (Select   hr110_factory_no factory_no, COUNT (hr110_worker_no) worker_no, hr110_sex sex
                      From hr110
                     Where hr110_company_no = 'GSM'
                       And hr110_sex = 'F'
                       And hr110_out_date Is Null
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) >= 3
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) <= 6
                  Group By hr110_factory_no, hr110_sex) bf,
                 (Select   hr110_factory_no factory_no, COUNT (hr110_worker_no) worker_no, hr110_sex sex
                      From hr110
                     Where hr110_company_no = 'GSM'
                       And hr110_sex = 'M'
                       And hr110_out_date Is Null
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) >= 3
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) <= 6
                  Group By hr110_factory_no, hr110_sex) bm
           Where bf.factory_no = bm.factory_no
        Group By bf.factory_no, bf.sex, bm.sex) b,
       (Select   cf.factory_no cf_factory_no, SUM (cf.worker_no) cf_total, cf.sex cf_sex, SUM (cm.worker_no) cm_total, cm.sex cm_sex
            From (Select   hr110_factory_no factory_no, COUNT (hr110_worker_no) worker_no, hr110_sex sex
                      From hr110
                     Where hr110_company_no = 'GSM'
                       And hr110_sex = 'F'
                       And hr110_out_date Is Null
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) >= 6
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) <= 12
                  Group By hr110_factory_no, hr110_sex) cf,
                 (Select   hr110_factory_no factory_no, COUNT (hr110_worker_no) worker_no, hr110_sex sex
                      From hr110
                     Where hr110_company_no = 'GSM'
                       And hr110_sex = 'M'
                       And hr110_out_date Is Null
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) >= 6
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) <= 12
                  Group By hr110_factory_no, hr110_sex) cm
           Where cf.factory_no = cm.factory_no
        Group By cf.factory_no, cf.sex, cm.sex) c,
       (Select   df.factory_no df_factory_no, SUM (df.worker_no) df_total, df.sex df_sex, SUM (dm.worker_no) dm_total, dm.sex dm_sex
            From (Select   hr110_factory_no factory_no, COUNT (hr110_worker_no) worker_no, hr110_sex sex
                      From hr110
                     Where hr110_company_no = 'GSM'
                       And hr110_sex = 'F'
                       And hr110_out_date Is Null
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) >= 12
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) <= 24
                  Group By hr110_factory_no, hr110_sex) df,
                 (Select   hr110_factory_no factory_no, COUNT (hr110_worker_no) worker_no, hr110_sex sex
                      From hr110
                     Where hr110_company_no = 'GSM'
                       And hr110_sex = 'M'
                       And hr110_out_date Is Null
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) >= 12
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) <= 24
                  Group By hr110_factory_no, hr110_sex) dm
           Where df.factory_no = dm.factory_no
        Group By df.factory_no, df.sex, dm.sex) d,
       (Select   ef.factory_no ef_factory_no, SUM (ef.worker_no) ef_total, ef.sex ef_sex, SUM (em.worker_no) em_total, em.sex em_sex
            From (Select   hr110_factory_no factory_no, COUNT (hr110_worker_no) worker_no, hr110_sex sex
                      From hr110
                     Where hr110_company_no = 'GSM'
                       And hr110_sex = 'F'
                       And hr110_out_date Is Null
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) >= 24
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) <= 36
                  Group By hr110_factory_no, hr110_sex) ef,
                 (Select   hr110_factory_no factory_no, COUNT (hr110_worker_no) worker_no, hr110_sex sex
                      From hr110
                     Where hr110_company_no = 'GSM'
                       And hr110_sex = 'M'
                       And hr110_out_date Is Null
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) >= 24
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) <= 36
                  Group By hr110_factory_no, hr110_sex) em
           Where ef.factory_no = em.factory_no
        Group By ef.factory_no, ef.sex, em.sex) e,
       (Select   ff.factory_no ff_factory_no, SUM (ff.worker_no) ff_total, ff.sex ff_sex, SUM (fm.worker_no) fm_total, fm.sex fm_sex
            From (Select   hr110_factory_no factory_no, COUNT (hr110_worker_no) worker_no, hr110_sex sex
                      From hr110
                     Where hr110_company_no = 'GSM'
                       And hr110_sex = 'F'
                       And hr110_out_date Is Null
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) >= 36
                  Group By hr110_factory_no, hr110_sex) ff,
                 (Select   hr110_factory_no factory_no, COUNT (hr110_worker_no) worker_no, hr110_sex sex
                      From hr110
                     Where hr110_company_no = 'GSM'
                       And hr110_sex = 'M'
                       And hr110_out_date Is Null
                       And TRUNC (MONTHS_BETWEEN (Sysdate, hr110_in_date)) >= 36
                  Group By hr110_factory_no, hr110_sex) fm
           Where ff.factory_no = fm.factory_no
        Group By ff.factory_no, ff.sex, fm.sex) f
Where a.af_factory_no = b.bf_factory_no
   And c.cf_factory_no = b.bf_factory_no
   And d.df_factory_no = b.bf_factory_no
   And e.ef_factory_no = b.bf_factory_no
   And f.ff_factory_no = b.bf_factory_no

第三種方式
select * from (
select hr110_factory_no factory_no1,count(*) cnt1
from hr110
where hr110_company_no = 'GSM'
   and hr110_out_date is null
   and months_between(sysdate,hr110_in_date) < 3
group by hr110_factory_no) A,
(
select hr110_factory_no factory_no2,count(*) cnt2
from hr110
where hr110_company_no = 'GSM'
   and hr110_out_date is null
   and months_between(sysdate,hr110_in_date) >= 3
   and months_between(sysdate,hr110_in_date) < 6
group by hr110_factory_no) B,
(
select hr110_factory_no factory_no3,count(*) cnt3
from hr110
where hr110_company_no = 'GSM'
   and hr110_out_date is null
   and months_between(sysdate,hr110_in_date) >= 6
   and months_between(sysdate,hr110_in_date) < 12
group by hr110_factory_no) C,
(
select hr110_factory_no factory_no4,count(*) cnt4
from hr110
where hr110_company_no = 'GSM'
   and hr110_out_date is null
   and months_between(sysdate,hr110_in_date) >= 12
   and months_between(sysdate,hr110_in_date) < 24
group by hr110_factory_no) D,
(
select hr110_factory_no factory_no5,count(*) cnt5
from hr110
where hr110_company_no = 'GSM'
   and hr110_out_date is null
   and months_between(sysdate,hr110_in_date) >= 12
   and months_between(sysdate,hr110_in_date) < 36
group by hr110_factory_no) E,
(
select hr110_factory_no factory_no6,count(*) cnt6
from hr110
where hr110_company_no = 'GSM'
   and hr110_out_date is null
   and months_between(sysdate,hr110_in_date) >= 36
group by hr110_factory_no) F,
(
select hr110_factory_no factory_no7,count(*) cnt7
from hr110
where hr110_company_no = 'GSM'
   and hr110_out_date is null
   and hr110_sex = 'M'
group by hr110_factory_no) G,
(
select hr110_factory_no factory_no8,count(*) cnt8
from hr110
where hr110_company_no = 'GSM'
   and hr110_out_date is null
   and hr110_sex = 'F'
group by hr110_factory_no) H
where A.FACTORY_NO1 = B.FACTORY_NO2
   and A.FACTORY_NO1 = C.FACTORY_NO3
   and A.FACTORY_NO1 = D.FACTORY_NO4
   and A.FACTORY_NO1 = E.FACTORY_NO5
   and A.FACTORY_NO1 = F.FACTORY_NO6
   and A.FACTORY_NO1 = G.FACTORY_NO7
   and A.FACTORY_NO1 = H.FACTORY_NO8