SQL subtotal sorting

I haven't written anything for a long time!

Schools	Class	Student ID	Name	Score
School	Class 01	01	Kobayashi	90
School	Class 01	02	Xiao Li	60
	Subtotal	Number of students	2	150
School	Class 02	01	Xiaoqiang	75
	Subtotal	Number of students	1	75
	Total schools	Number of students	3	225
School B	Class 01	01	Small	85
	Subtotal	Number of students	1	85
	Total schools	Number of students	1	85
	Total County	Number of schools: 2	Number of students: 4	Total: 305

In such a list, subtotal and total appear frequently in statistical reports. In these reports, how to sort is a problem!

In the above table, the students are sorted by student numbers, and the classes are also sorted by size, but the schools are not sorted by size, but by fixed order. In this case, then we need to manually create a sequence number for the school to facilitate their sorting.

But how can we insert "Class subtotal", "school total", and "County total" to the correct position?

Our normal sorting is:Order by school, class, student ID

• "Class subtotal" is to be placed behind the last student in a class, and the last digit is the number. We can find out, but in this case, the last bit of judgment is made every time! This is obviously inefficient and cumbersome.
• "School total" is the last class information of a school.
• "County total" is the last place to put the school information in a certain county.

We can see that ,"Last digit"This is the key. How can we determine the last one? The teacher can help us arrange the degree. Why can't we arrange the subtotal directly at the end? The school can arrange the class to take the lead. Can it arrange the sum directly at the end? So

• "Class subtotal" order by school, class
• "School total" order by school

The sorting position of the total subtotal is determined by the statistical range. In short,

"School A-> class 01-> class total column" directly gives "class 010" such information, through order by school, class, the "class note" of the class 01 will be placed at the end of the class (of course, it is not to say that "0" must be added at the end of the class. It is up to you to decide what to add, for more information, see,For Oracle, the default value of null is the maximum value.Do not let the class notes of class 01 fall into other classes ).

"School A-> school total" directly gives "school A0" such information for the same reason.

 

Schools	Class	County-level sorting Value (Custom)	School sorting Value	Class sorting Value	Student ID	Name	Score
School	Class 01	01	School	Class 01	01	Kobayashi	90
School	Class 01	01	School	Class 01	02	Xiao Li	60
	Subtotal	01	School	Class 010	Number of students	2	150
School	Class 02	01	School	Class 02	01	Xiaoqiang	75
	Subtotal	01	School	Class 020	Number of students	1	75
	Total schools	01	School A0		Number of students	3	225
School B	Class 01	02	School B	Class 01	01	Small	85
	Subtotal	02	School B	Class 010	Number of students	1	85
	Total schools	02	School B0		Number of students	1	85
	Total County	99			Number of schools: 2	Number of students: 4	Total: 305

Pay attention to the blue font in it. This is simple.

Order county level sorting value, school sorting value, class sorting value, student ID

General idea:

Select to adjust the display content

From (

Select * from

(Select student information, student ID, class, school, county as level sorting value, school as school sorting value, class as class sorting value, type identification (used to distinguish data layers)

Union all

Select class subtotal (count (student ID), sum (score), null class sorting value ,.............

Group by county level, school, class

Union all

Select school total (count (student ID), sum (score), null school sorting value ,.............

Group by county level, school

Union all

Select County total (count (student ID), sum (score), null county level sorting value ,.............)

Group by county level

Order county level sorting value, school sorting value, class sorting value, student ID

)

Of course, this is okay when the data volume is small. If the data volume is large, it is best to cache the statistical data in various processing methods. Do not repeat the time-consuming operations such as sum and count.

-------------------------------------- Cute split line ---------------------------------

For statistics like above, there is also a simpler method, that is, using group by rollup (school, class, (student ID, name) or group by rollup (school, class, student ID), Name field with max (name) as name

Schools	Class	Student ID	Name	Score (SUM)	Count)	Grouping_id(School, class, student ID)
School	Class 01	01	Kobayashi	90	1	0
School	Class 01	02	Xiao Li	60	1	0
School	Class 02	01	Xiaoqiang	75		0
School B	Class 01	01	Small	85		0
School	Class 01			150	2	1
School	Class 02			75	1	1
School B	Class 01			85	1	1
School				225	3	3
School B				85	1	3
				305	4	7

As mentioned above, in Oracle sorting, null is the maximum value by default. Therefore, if it is in ascending orderOrder by school, class, student IDThat's all. It's very convenient.

Grouping_id() The method is used in combination with group by rollup. Here we are used to differentiate data layers. We can see numbers such as 0, 1, 3, and 7, which may not be easy to understand,

But it is easy to understand how to convert to binary. 111 = 7 011 = 3 001 = 1 000 = 0. In short, the larger the range of statistics, the larger the number,

Group by rollup (school, class, (student ID, name ))

Split into group by school, class, (student ID, name) + group by school, class + group by school + group by null

Grouping_id(School, class, student ID) 000 001 011 111

General idea:

Select to adjust the display content

From (

Select school, class, student ID, name, sum (score) score, count (student ID) Student ID,Grouping_id(School, class, student ID) gp_num

......................

Group by rollup (school, class, (student ID, name ))

Order by school, class, student ID

)

This writing method is less efficient than the preceding writing method, but it is much more concise. Of course, the sorting priority of null values for different databases is inconsistent. The first method is more versatile, but the code is very long.