A simple method of ORACLE Multi-conditional Statistical query

A few days ago to do a statistical query function, because involving multiple tables, a variety of conditions of statistical analysis. At the beginning, I thought of UNION and IF statements, and then wrote more than 1000 lines of code, just to check 30 pieces of data.

Then start Baidu, statistics under a variety of conditions. And then there was a grammar that caught my eye, case when then else end.

When the conditions set by CASE are met, the then statement can be executed. Since the statistics of the grouping query I want to do is to list each situation, and different results are returned according to the "administrator number" entered, the number of result records and each case are known, this syntax is completely available.

The core code is as follows:

SELECT SUBSTR (A. Business, 1BI 2) Administrative area, SUBSTR (A. Business, 3M Length (A. Business)-2) business module, A. The case is closed, A. In the process, A. Total number of cases

ROUND (A. Closed case / decode (A. The total number of cases, 09999A. Total number of cases), 4) * 100 | |'% 'completion rate

FROM

(SELECT 'Municipal lawyer Service' business

1 sort

SUM (CASE WHEN county code IS NULL AND (TRUNC ('DD') BETWEEN V_SJQ AND V_SJZ) AND application status code IN (' ztdm1','ztdm2') THEN 1 ELSE 0 END) closed the case

SUM (CASE WHEN county code IS NULL AND (TRUNC ('DD') BETWEEN V_SJQ AND V_SJZ) AND application status code NOT IN (' ztdm1','ztdm2') THEN 1 ELSE 0 END) processing

Total number of SUM (CASE WHEN District Code IS NULL AND (TRUNC ('DD') BETWEEN V_SJQ AND V_SJZ) AND Application status Code IS NOT NULL THEN 1 ELSE 0 END)

Lawyer FROM TA_ applies for entrustment

) A

WHERE A. Sort IN (V _ sort 1 Magi V _ sort 2 Magi V _ sort 3 Magi V _ sort 4 Magi V _ sort 5)

ORDER BY A. Sort

The range of data queried by different administrators is controlled by sorting numbers:

V _ sort 1 INTEGER; V _ sort 2 INTEGER; V _ sort 3 INTEGER; V _ sort 4 INTEGER; V _ sort 5 INTEGER;-- SELECT DECODE (P _ administrator number, 'test1',2,' test2',3, 'test3',4,' test4',5) INTO V _ sort 1 FROM DUAL;V_ sort 2 INTEGER; V _ sort 1 "6 V _ sort 3 "V _ sort 1" 12 × V _ sort 4 "V" V _ sort 1 "18 bot V _ sort 5" V _ sort 1 "24"

Summary

At present, the amount of test data is small, and there is no obvious difference in performance before and after optimization, but the number of lines of code is reduced by more than 3 times, the readability is significantly enhanced, a lot of IF judgment is missing, and the theoretical complexity is greatly reduced.

The above is the whole content of the simple method of ORACLE multi-conditional statistical query in this paper. I hope it will be helpful to you. Interested friends can refer to: oracle database startup phase analysis, oracle virtual private database detailed introduction, Oracle paging query performance optimization code, etc., if you have any questions, you can leave a message at any time, the editor will reply to you in time. Thank you for your support to the website!