oracle函数decode的应用

题目

A表：

AHDM	AJNYD	FYDM	ND
1001	1	330500	2022
1002	2	330500	2022
1003	3	330500	2022
1004	1	330505	2022
1005	2	330505	2022
1006	3	330505	2022

AHDM：案号代码，唯一

AJNYD：区分 简案：AJNYD=1、普案：AJNYD=2、繁案：AJNYD=3

FYDM：法院代码，法院标识

ND：年度

B表：

AHDM	SJTS
1001	5
1002	8
1003	4
1004	3
1005	16
1006	10

AHDM：案号代码，唯一

SJTS：审理天数

题目：统计各法院今年简、普、繁案件数量、占比、平均审理天数。要求：查询次数尽可能的少。

占比：

简案/(简案+普案+繁案)，保留2位小数

普案/(简案+普案+繁案)，保留2位小数

繁案/(简案+普案+繁案)，保留2位小数

平均审理天数：

简案总审理天数/简案总数，保留2位小数

普案总审理天数/普案总数，保留2位小数

繁案总审理天数/繁案总数，保留2位小数

回答

表A

#建表语句（实际开发过程中需要给字段备注信息）
CREATE
    TABLE
        ZZZZ_A(
            AHDM NUMBER ,
            AJNYD NUMBER ,
            FYDM NUMBER ,
            ND NUMBER
        )
;

#插入数据语句
INSERT INTO ZZZZ_A( AHDM , AJNYD , FYDM , ND ) SELECT 1001,1,330500,2022 from DUAL;
INSERT INTO ZZZZ_A( AHDM , AJNYD , FYDM , ND ) SELECT 1002,2,330500,2022 from DUAL;
INSERT INTO ZZZZ_A( AHDM , AJNYD , FYDM , ND ) SELECT 1003,3,330500,2022 from DUAL;
INSERT INTO ZZZZ_A( AHDM , AJNYD , FYDM , ND ) SELECT 1004,1,330505,2022 from DUAL;
INSERT INTO ZZZZ_A( AHDM , AJNYD , FYDM , ND ) SELECT 1005,2,330505,2022 from DUAL;
INSERT INTO ZZZZ_A( AHDM , AJNYD , FYDM , ND ) SELECT 1006,3,330505,2022 from DUAL;

表B

#建表语句（实际开发过程中需要给字段备注信息）
CREATE
    TABLE
        ZZZZ_B(
            AHDM NUMBER ,
            SJTS NUMBER
        )
;

#插入数据语句
INSERT INTO ZZZZ_B( AHDM , SJTS ) SELECT 1001,5 from DUAL;
INSERT INTO ZZZZ_B( AHDM , SJTS ) SELECT 1002,8 from DUAL;
INSERT INTO ZZZZ_B( AHDM , SJTS ) SELECT 1003,4 from DUAL;
INSERT INTO ZZZZ_B( AHDM , SJTS ) SELECT 1004,3 from DUAL;
INSERT INTO ZZZZ_B( AHDM , SJTS ) SELECT 1005,16 from DUAL;
INSERT INTO ZZZZ_B( AHDM , SJTS ) SELECT 1006,10 from DUAL;

sql

SELECT
	AJNYD1,
	AJNYD2,
	AJNYD3,
	ROUND(AJNYD1/AJNYD_SUM,2) AJNYD1_PRO,
	ROUND(AJNYD2/AJNYD_SUM,2) AJNYD2_PRO,
	ROUND(AJNYD3/AJNYD_SUM,2) AJNYD3_PRO,
	ROUND(SJTS_SUM1/AJNYD1,2) AVE_DAY1,
	ROUND(SJTS_SUM2/AJNYD1,2) AVE_DAY2,
	ROUND(SJTS_SUM3/AJNYD1,2) AVE_DAY3
FROM( 
	SELECT
		COUNT(1) AJNYD_SUM,
		SUM(DECODE(AJNYD,1,1,0)) AJNYD1,
		SUM(DECODE(AJNYD,2,1,0)) AJNYD2,
		SUM(DECODE(AJNYD,3,1,0)) AJNYD3,
		SUM(DECODE(AJNYD,1,SJTS,0)) SJTS_SUM1,
		SUM(DECODE(AJNYD,2,SJTS,0)) SJTS_SUM2,
		SUM(DECODE(AJNYD,3,SJTS,0)) SJTS_SUM3
	FROM
		ZZZZ_A a
		JOIN ZZZZ_B b ON a.AHDM=b.AHDM
)
;