记几条oracle开窗函数的使用

-- 各个工种下的人数
t1:
select
h.work_type as work_type,
1 as n
from
js_gkb_handyman h
where h.DELETED = 0

t2:
select
w.TYPE_NAME,
sum(n) over(partition by work_type) as num
from
(select
h.work_type as work_type,
1 as n
from
js_gkb_handyman h
where h.DELETED = 0)t1
left join JS_GKB_WORK_TYPE w ON w.TYPE_CODE = t1.work_type and w.DELETED = 0

t3:
SELECT
t2.TYPE_NAME,
t2.num
FROM
(
select
w.TYPE_NAME,
sum(n) over(partition by work_type) as num
from
(select
h.work_type as work_type,
1 as n
from
js_gkb_handyman h
where h.DELETED = 0)t1
left join JS_GKB_WORK_TYPE w ON w.TYPE_CODE = t1.work_type and w.DELETED = 0
)t2
WHERE t2.TYPE_NAME is not null
group by num,TYPE_NAME

-- 各个工种下申报的人
t1:
select
d.WORK_TYPE as work_type,
1 as n
from
JS_GKB_DECRALE d
where d.DELETED = 0 and d.DECRALE_DATE = '2020'

t2:
select
w.type_name,
sum(n) over(partition by work_type) as num
FROM
(
select
d.WORK_TYPE as work_type,
1 as n
from
JS_GKB_DECRALE d
where d.DELETED = 0 and d.DECRALE_DATE = '2020' and (d.STUTS = '2' or d.STUTS = '4')
)t1
left join JS_GKB_WORK_TYPE w ON w.TYPE_CODE = t1.work_type and w.DELETED = 0

t3:
select
*
from
(select
w.type_name,
sum(n) over(partition by work_type) as num
FROM
(
select
d.WORK_TYPE as work_type,
1 as n
from
JS_GKB_DECRALE d
where d.DELETED = 0 and d.DECRALE_DATE = '2020' and (d.STUTS = '2' or d.STUTS = '4')
)t1
left join JS_GKB_WORK_TYPE w ON w.TYPE_CODE = t1.work_type and w.DELETED = 0)t2
where type_name is not null
group by type_name,num

t4:
select
sum(num)
from
(select
*
from
(select
w.type_name,
sum(n) over(partition by work_type) as num
FROM
(
select
d.WORK_TYPE as work_type,
1 as n
from
JS_GKB_DECRALE d
where d.DELETED = 0 and d.DECRALE_DATE = '2020' and (d.STUTS = '2' or d.STUTS = '4')
)t1
left join JS_GKB_WORK_TYPE w ON w.TYPE_CODE = t1.work_type and w.DELETED = 0)t2
where type_name is not null
group by type_name,num)t3

-- 统计各个等级申报人数
t1:
select
d.DECRALE_GRADE,
1 as n
from
JS_GKB_DECRALE d
WHERE d.DELETED = 0 and d.DECRALE_DATE = '2020' and (d.STUTS = '2' or d.STUTS = '4')

t2:
SELECT
DECRALE_GRADE,
sum(n) over(partition by DECRALE_GRADE) as NUM
FROM
(select
d.DECRALE_GRADE,
1 as n
from
JS_GKB_DECRALE d
WHERE d.DELETED = 0 and d.DECRALE_DATE = '2020' and (d.STUTS = '2' or d.STUTS = '4'))t1


t3:
SELECT
*
FROM
(SELECT
DECRALE_GRADE,
sum(n) over(partition by DECRALE_GRADE) as NUM
FROM
(select
d.DECRALE_GRADE,
1 as n
from
JS_GKB_DECRALE d
WHERE d.DELETED = 0 and d.DECRALE_DATE = '2020' and (d.STUTS = '2' or d.STUTS = '4'))t1)t2
group by DECRALE_GRADE,NUM