This is a recent project about SQL statements, this article simply records and summarizes the following knowledge points:
1. How to count the total number of a field in a table, such as the number of students in different "majors" and their percentage;
2. How to contact another table to query the total number and percentage of a field;
3. A brief introduction to the decode to prevent the denominator of 0 and Trunc reserve the number of decimal digits and other functions;
4. Often complex SQL statements involve cross-linking and vertical connection of query results, as described here.
Recently bought this "Oracle query optimization rewriting skills and cases NCLB", recommended that you read the next. I will also add some knowledge of relevant data, hope to help you! The article is mainly based on basic knowledge, at the same time, mainly to solve practical problems. At the same time I use self-answer to ask the new narrative method to introduce ~
0. Preface
If there is now a table as shown, this table is very common in the database, including fields (serial number, teacher name, unit name, gender, education). The information for this table is: Test_teacher (id,name,dw_name,sex,degree).
1.
question one: How to count totals and proportions in a single table
It is now necessary to count the number of teachers in each unit and the number of people with different qualifications and gender in the unit. This is a very common problem, when the project is a lot of statistics, including: Age, Learning edge, title, Professional and so on.
This is very simple and a lot of methods, common mainly using subqueries or group by groups.
GROUP BY:
Select Dw_name, COUNT (dw_name) as number of from Test_teachergroup by Dw_nameorder by Dw_name;
the output is as follows: where order BY is alphabetical output.
If there is a need to count the number of male and female teachers in each college, what should be done about the number of PhD, master and bachelor teachers? Because it's all about the same table, one way is to use a subquery, and here's another way to do it through case.
Case and then
:
Select Dw_name, COUNT (dw_name) as SUM, count (case sex= ' male ' then 1 end) as Mans, Count (case when sex= ' female ' then 1 end) As women, count (case when degree= ' Dr ' then 1 end) as BS, count (case when degree= ' master ' then 1 END) as Ssfrom Test_teach ER
the output is as follows: Indicates how sex is "male", statistics plus 1.
Now, if we need to count the proportion of the total number of people in each college, how do we calculate it?
Traditional methods This step is to count two statistics, then processed by the background Java or C + +, or by using division: Total number of college/teacher tables, but already used group by, if used again to count the total number of Test_teacher will be an error.
This is accomplished with the function ratio_to_report () over () of Oracle.
Ratio_to_report:
Select Dw_name, COUNT (dw_name) as SUM, Ratio_to_report (count (1)) over (), COUNT (case when sex= ' man ' then 1 END) as Ma N, COUNT (case "sex= ' then 1 END) as Womenfrom Test_teacher
the output is as follows: of which the computer 3 teachers, a total of 10 teachers, the proportion accounted for 0.3.
2. Question two: Contact another table statistics Total and proportion
In the design of the database process, the teacher's information is usually set to a table, the school information will exist in another table, the same lesson information, published paper information will have a separate table, and then through the teacher name (or teacher number) connection query.
So here's how the second statistic totals and proportions are described, assuming there is also a college table, as shown in: The table TEST_DEP structure is (DW_NAME,DW_CODE,YEAR,ADDR).
Typically, you traverse the Unit table, and then the subquery joins the teacher table to count the number of people in different units. The same is true for the statistics of Professional teachers, the publication of papers by teachers and the publication of papers by colleges.
Two-table connection subquery
: Total Statistics
Select T2. Dw_name, (select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name) as Total, (select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name and sex= ' male ') as male, (select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name and sex= ' female ') as female from TEST_DEP T2;
The output looks like this: This approach is a very common statistical method, not just through a single table, because databases tend to contact other tables through foreign keys.
Another advantage of this two-table linkage is that it is more convenient to calculate percentages, ratios, and so on, since the same table is cumbersome to use two times, such as "SELECT (SELECT * from T1) from T1;" The sub-query is used here to calculate the scale code below.
Two-table concatenated subqueries: Statistical scale
Select T2. Dw_name, (select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name) as Total, ((select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name) /(SELECT COUNT (*) from Test_teacher) as the total percentage, (SELECT COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name and sex= ' male ') as male, (select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name and sex= ' female ') as female, ((select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name and sex= ' man ') /(select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name) as male teacher ratio from TEST_DEP T2;
The output looks like this:
A total of 10 teachers, computer College 3 people, the proportion of =3/10=0.4, male teachers in the proportion of the computer college male teachers 2, female teachers 1 people, so proportion =2/3=0.666, the method used here is the division of sub-query. In fact, the more common approach is to get the total, and then the background business logic is calculated.
3. Question three: Division prevents the denominator from 0 and preserves the number of significant digits
if the output is 0.6666667, you need to preserve the number of significant digits, the method here is to use the trunc () function, and the integer can be used in the floor () function.
TRUNC (X[,y]) function: Calculates the x value of truncated to Y decimal places, y defaults to 0, and the result becomes an integer value.
trunc () is a truncation operation, Floor (x) is the largest integer less than or equal to X.
TRUNC () floor ():
Select T2. Dw_name, (select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name) as Total, (select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name and sex= ' male ') as male, (select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name and sex= ' female ') as female, trunc (((Select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name and sex= ' man ') /(select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. dw_name)) *100,2) as percent from TEST_DEP T2;
The output looks like this:
The output is 66.66, which indicates the percentage of male teachers in Computer Academy, and the method of retaining 2 digits is: trunc (statistics male Teacher sub-query/Statistics teacher subquery *100,2).
Oracle often requires statistics such as the percentage of boys in the total number of the class, and if the denominator is 0, it will error "[ERR] ora-01476:divisor is equal to zero". What about that?
Workaround: Use the function decode, when the denominator is 0 o'clock to return 0, otherwise divide the operation.
Select a/b from C; Modify it as follows: Select decode (b, 0, 0, A/b) from C;
Example: Decode (xf_all, 0, 0,trunc (xf_math/xf_all * 1)) as BL
The above sub-query division uses decode and combines trunc to retain 2 significant digits of SQL as follows.
decode ():
Select T2. Dw_name, (select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name) as Total, (select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name and sex= ' male ') as male, (select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name and sex= ' female ') as female, trunc (Decode ((select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. dw_name), 0, 0, ((select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. Dw_name and sex= ' man ') /(select COUNT (*) from Test_teacher t1 where T1. Dw_name=t2. dw_name))) *100,2) as percent from TEST_DEP T2;
The output results as shown in the core method: Trunc (Decode (b,0,0,a/b) *100, 2).
4. Question four: querying for horizontal and vertical connections
Complex SQL statements typically include both horizontal and vertical connections.
cross-linking: using custom subqueries
The method used here is a subquery custom named method, which returns only one row of data, usually in JSON format to the background, only need to display in the background. Its advantage is that no matter how complex the table is, or whether the table is not related, it only needs to count the values in one row, which can be contacted to return the results.
As shown, this is the result of a horizontal connection return, but you need to know the specific academy name.
It is better to use this method when counting the total information for different tables.
select t1. Dw_name, T2. ZS, T3.bs, T4. Dw_name, T5. ZS, T6. Bsfrom (select Dw_name as Dw_name from Test_teacher where dw_name= ' software Academy ' GROUP by Dw_name) T1, (select COUNT (*) as ZS from Test_teacher where dw_name= ' Software academy ') T2, (select COUNT (*) as BS from Test_teacher where dw_name= ' software academy ' and Degree= ' PhD ') T3, (s Elect Dw_name as Dw_name from Test_teacher where dw_name= ' Computer Academy ' GROUP by Dw_name) T4, (select COUNT (*) as ZS from Test_tea CHER where dw_name= ' Computer Academy ') T5, (select COUNT (*) as BS from Test_teacher where dw_name= ' Computer Academy ' and Degree= ' PhD ') T6;
Vertical connection: Using union ALL connection
Assuming that you now only need to count the information for the Software Academy, Computer Academy, which requires a vertical connection, use union ALL to connect naturally.
union: Perform union operations on two result sets, excluding duplicate rows, and sorting the default rules;
Union All: Perform a set operation on two result sets, including repeating rows, without sorting.
Note: Count needs to use Group by, and the advantage of this method is that if there is a lot of NULL unit information in the table or just a few fixed college information, it is more appropriate to use this method at this point.
in summary, specific processing methods are required for a particular occasion.
Select Dw_name, COUNT (dw_name) as SUM, Ratio_to_report (count (1)) over () as BL, count (case when sex= ' men ' then 1 END) As Man, COUNT (case when sex= ' woman ' then 1 END) as Womenfrom test_teacher WHERE dw_name= ' software Academy ' GROUP by Dw_name UNION All Select Dw_name, COUNT (dw_name) as SUM, Ratio_to_report (count (1)) over () as BL,
the output looks like this:
Related information:
Database SQL statement Select Simple Record summary
Database SQL query statement table row and column conversions and one row of data into two columns
Database Navicat for Oracle Basic usage graphic INTRODUCTION
Database Navicat for Oracle set up uniqueness and incremental sequence experiments
finally hope that the article is helpful to you, this is an article of my online notes, at the same time after combining their actual project and SQL performance optimization, will share some more professional articles ~ There are more than 20 days to graduate as a teacher, haha!
(By:eastmount late 2016-06-14 3 o'clock http://blog.csdn.net//eastmount/)
Database Total Oracle Single-table queries and percentages and data horizontal vertical connections
