/*******************************
--創建勞動保障公務案件統計表視圖20b1
*******************************/
--勞動保障公務案件統計表第一行合計項
select 1 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'05',1,0)) col5
			,sum(decode(ajlb,'06',1,0)) col6,sum(decode(ajlb,'07',1,0)) col7,sum(decode(ajlb,'08',1,0)) col8
			,sum(decode(ajlb,'09',1,0)) col9,sum(decode(ajlb,'10',1,0)) col10,sum(decode(ajlb,'11',1,0)) col11
			,sum(decode(ajlb,'12',1,0)) col12,sum(decode(ajlb,'13',1,0)) col13,sum(decode(ajlb,'14',1,0)) col14
			,sum(decode(ajlb,'04',1,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col15
			,sum(zlgzjs) col16,sum(xzcljs) col17,sum(xzcfjs) col18,sum(jgjs) col19,sum(fkjs) col20
			,sum(qtjs) col21
	from v_a1
 where 1=1
/
--勞動保障公務案件統計表第2行分類統計
select 2 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'05',1,0)) col5
			,sum(decode(ajlb,'06',1,0)) col6,sum(decode(ajlb,'07',1,0)) col7,sum(decode(ajlb,'08',1,0)) col8
			,sum(decode(ajlb,'09',1,0)) col9,sum(decode(ajlb,'10',1,0)) col10,sum(decode(ajlb,'11',1,0)) col11
			,sum(decode(ajlb,'12',1,0)) col12,sum(decode(ajlb,'13',1,0)) col13,sum(decode(ajlb,'14',1,0)) col14
			,sum(decode(ajlb,'04',1,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col15
			,sum(zlgzjs) col16,sum(xzcljs) col17,sum(xzcfjs) col18,sum(jgjs) col19,sum(fkjs) col20
			,sum(qtjs) col21
	from v_a1
 where 1=1 and dwxz = '01'
/
--勞動保障公務案件統計表第3行分類統計
select 3 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'05',1,0)) col5
			,sum(decode(ajlb,'06',1,0)) col6,sum(decode(ajlb,'07',1,0)) col7,sum(decode(ajlb,'08',1,0)) col8
			,sum(decode(ajlb,'09',1,0)) col9,sum(decode(ajlb,'10',1,0)) col10,sum(decode(ajlb,'11',1,0)) col11
			,sum(decode(ajlb,'12',1,0)) col12,sum(decode(ajlb,'13',1,0)) col13,sum(decode(ajlb,'14',1,0)) col14
			,sum(decode(ajlb,'04',1,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col15
			,sum(zlgzjs) col16,sum(xzcljs) col17,sum(xzcfjs) col18,sum(jgjs) col19,sum(fkjs) col20
			,sum(qtjs) col21
	from v_a1
 where 1=1 and dwxz = '02'
/
--勞動保障公務案件統計表第4行分類統計
select 4 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'05',1,0)) col5
			,sum(decode(ajlb,'06',1,0)) col6,sum(decode(ajlb,'07',1,0)) col7,sum(decode(ajlb,'08',1,0)) col8
			,sum(decode(ajlb,'09',1,0)) col9,sum(decode(ajlb,'10',1,0)) col10,sum(decode(ajlb,'11',1,0)) col11
			,sum(decode(ajlb,'12',1,0)) col12,sum(decode(ajlb,'13',1,0)) col13,sum(decode(ajlb,'14',1,0)) col14
			,sum(decode(ajlb,'04',1,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col15
			,sum(zlgzjs) col16,sum(xzcljs) col17,sum(xzcfjs) col18,sum(jgjs) col19,sum(fkjs) col20
			,sum(qtjs) col21
	from v_a1
 where 1=1 and dwxz = '03'
/
--勞動保障公務案件統計表第5行分類統計
select 5 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'05',1,0)) col5
			,sum(decode(ajlb,'06',1,0)) col6,sum(decode(ajlb,'07',1,0)) col7,sum(decode(ajlb,'08',1,0)) col8
			,sum(decode(ajlb,'09',1,0)) col9,sum(decode(ajlb,'10',1,0)) col10,sum(decode(ajlb,'11',1,0)) col11
			,sum(decode(ajlb,'12',1,0)) col12,sum(decode(ajlb,'13',1,0)) col13,sum(decode(ajlb,'14',1,0)) col14
			,sum(decode(ajlb,'04',1,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col15
			,sum(zlgzjs) col16,sum(xzcljs) col17,sum(xzcfjs) col18,sum(jgjs) col19,sum(fkjs) col20
			,sum(qtjs) col21
	from v_a1
 where 1=1 and dwxz = '04'
/
--勞動保障公務案件統計表第6行分類統計
select 6 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'05',1,0)) col5
			,sum(decode(ajlb,'06',1,0)) col6,sum(decode(ajlb,'07',1,0)) col7,sum(decode(ajlb,'08',1,0)) col8
			,sum(decode(ajlb,'09',1,0)) col9,sum(decode(ajlb,'10',1,0)) col10,sum(decode(ajlb,'11',1,0)) col11
			,sum(decode(ajlb,'12',1,0)) col12,sum(decode(ajlb,'13',1,0)) col13,sum(decode(ajlb,'14',1,0)) col14
			,sum(decode(ajlb,'04',1,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col15
			,sum(zlgzjs) col16,sum(xzcljs) col17,sum(xzcfjs) col18,sum(jgjs) col19,sum(fkjs) col20
			,sum(qtjs) col21
	from v_a1
 where 1=1 and dwxz = '05'
/
--勞動保障公務案件統計表第7行分類統計
select 7 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'05',1,0)) col5
			,sum(decode(ajlb,'06',1,0)) col6,sum(decode(ajlb,'07',1,0)) col7,sum(decode(ajlb,'08',1,0)) col8
			,sum(decode(ajlb,'09',1,0)) col9,sum(decode(ajlb,'10',1,0)) col10,sum(decode(ajlb,'11',1,0)) col11
			,sum(decode(ajlb,'12',1,0)) col12,sum(decode(ajlb,'13',1,0)) col13,sum(decode(ajlb,'14',1,0)) col14
			,sum(decode(ajlb,'04',1,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col15
			,sum(zlgzjs) col16,sum(xzcljs) col17,sum(xzcfjs) col18,sum(jgjs) col19,sum(fkjs) col20
			,sum(qtjs) col21
	from v_a1
 where 1=1 and dwxz = '06'
/
--勞動保障公務案件統計表第8行分類統計
select 8 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'05',1,0)) col5
			,sum(decode(ajlb,'06',1,0)) col6,sum(decode(ajlb,'07',1,0)) col7,sum(decode(ajlb,'08',1,0)) col8
			,sum(decode(ajlb,'09',1,0)) col9,sum(decode(ajlb,'10',1,0)) col10,sum(decode(ajlb,'11',1,0)) col11
			,sum(decode(ajlb,'12',1,0)) col12,sum(decode(ajlb,'13',1,0)) col13,sum(decode(ajlb,'14',1,0)) col14
			,sum(decode(ajlb,'04',1,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col15
			,sum(zlgzjs) col16,sum(xzcljs) col17,sum(xzcfjs) col18,sum(jgjs) col19,sum(fkjs) col20
			,sum(qtjs) col21
	from v_a1
 where 1=1 and dwxz = '07'
/
--勞動保障公務案件統計表第9行分類統計
select 9 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'05',1,0)) col5
			,sum(decode(ajlb,'06',1,0)) col6,sum(decode(ajlb,'07',1,0)) col7,sum(decode(ajlb,'08',1,0)) col8
			,sum(decode(ajlb,'09',1,0)) col9,sum(decode(ajlb,'10',1,0)) col10,sum(decode(ajlb,'11',1,0)) col11
			,sum(decode(ajlb,'12',1,0)) col12,sum(decode(ajlb,'13',1,0)) col13,sum(decode(ajlb,'14',1,0)) col14
			,sum(decode(ajlb,'04',1,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col15
			,sum(zlgzjs) col16,sum(xzcljs) col17,sum(xzcfjs) col18,sum(jgjs) col19,sum(fkjs) col20
			,sum(qtjs) col21
	from v_a1
 where 1=1 and dwxz = '08'
/
--補充資料
select 10 xh,sum(qzzxjs) col1,'' col2,'' col3,sum(fkje) col4
	from v_a1
 where 1=1
/