b1.sql

公务员管理系统

/*******************************
--创建劳动保障公务案件统计表视图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
/