Data aggregation and sorting

The program selects the following fields: Project name, project year, city, applicant, and supported amount.
The summary field is the project year, City
The query result column must be sorted by the required project name, project year, city, applicant unit, and supported amount.
Sorting by specified fields
Results to be generated:
========================================================== ======================================
No. Project name project annual Local Application Support amount
1 Name 1 2004 Provincial direct 1 company 100
2 name 2 2004 Provincial direct 2 company 100
3 2004 province total 200
4 name 3 2004 Shijiazhuang 3 company 100
5 2004 Shijiazhuang total 100
6 2004 total 300
7 names 3 2005 Shijiazhuang 3 companies 100
8 2005 Shijiazhuang total 100
9 2005 total 100
10 total 400
========================================================== ======================================

Bytes ------------------------------------------------------------------------------------

-- Assume that the following temporary table is obtained after the first summary + sorting (the first processing does not process subtotal, total, etc)

Create table # t (project name varchar (10), project annual varchar (10), City varchar (10), applicant unit varchar (10), Support amount int)
Insert # t select 'name 1', '123', 'provincial direction', '1 company', 2004
Union all select 'name 2', '123', 'provincial direction', '2 companies', 2004
Union all select 'name 3', '123', 'shijiazhuang ', '3 company', 2004
Union all select 'name 3', '123', 'shijiazhuang ', '3 company', 2005
Go

-- Processing is as follows:
Select project name, project year, city, applicant, supported amount
From (-- This is the summary part (excluding subtotal, the summary of the total processing part)
Select *
, S1 = 0, s2 = project year, s3 = 0, s4 = city, s5 = 0, s6 = Applicant unit, s7 = 0 -- this is required for sorting
From # t
Union all
-- This is the subtotal to achieve the summary, in the total part
Select project name = case
When grouping (project year) = 1 then 'Total'
Else ''end
, Project year = case
When grouping (project year) = 0
And grouping (city) = 1
Then project year + 'Total'
Else ''end
, City = case
When grouping (project year) = 0
And grouping (city) = 0
And grouping (Applicant) = 1
Then City + 'Total'
Else ''end
, Application unit = case
When grouping (Applicant) = 0 then applicant + 'Total'
Else ''end
, Supported amount = sum (supported amount)
, Grouping (project year), project year -- here and below are required for sorting
, Grouping (prefecture City), prefecture City
, Grouping (applicant), applicant
, Grouping (Applicant) + 1
From # T
Group by project year, city, applicant with rollup -- this is the grouping field for generating subtotal, total
)
Order by S1, S2, S3, S4, S5, S6, S7, supported amount -- S1 ~ S6 is required to prevent subtotal, and the amount supported later is required for other sorting.
Go

-- Delete the test temporary table
Drop table # T

/* -- Test Result

Project name project annual Local Application Support amount
---------------------------------------------------------------
Name 1 2004 Provincial direct 1 company 400
1 company Total 400
Name 2 2004 Provincial direct 2 Company 300
2 companies totaling 300
Total province-level transactions: 700
Name 3 2004 Shijiazhuang 3 company 200
3 companies totaling 200
Shijiazhuang total 200
2004 total 900
Name 3 2005 Shijiazhuang 3 company 100
3 companies totaling 100
Shijiazhuang total 100
2005 total 100
Total 1000

(14 rows are affected)
--*/

-- The above is based on the project year, city, application unit for Subtotal, total
-- The following is a subtotal, total, which is based on the project year and city, which makes it easier to see the rule.
-- Processing is as follows:
Select project name, project year, city, applicant, supported amount
From (-- This is the summary part (excluding subtotal, the summary of the total processing part)
Select *
, S1 = 0, s2 = project year, s3 = 0, s4 = city, s5 = 0 -- this is required for sorting
From # t
Union all
-- This is the subtotal to achieve the summary, in the total part
Select project name = case
When grouping (project year) = 1 then 'Total'
Else ''end
, Project year = case
When grouping (project year) = 0
And grouping (city) = 1
Then project year + 'Total'
Else ''end
, City = case
When grouping (city) = 0
Then City + 'Total'
Else ''end
,''
, Supported amount = sum (supported amount)
, Grouping (project year), project year -- here and below are required for sorting
, Grouping (prefecture City), prefecture City
, Grouping (city) + 1
From # t
Group by project year, city with rollup -- this is the grouping field for generating subtotal, total
)
Order by s1, s2, s3, s4, s5, supported amount -- s1 ~ S6 is required to prevent subtotal, and the amount supported later is required for other sorting.

/* -- Test Result

Project name project annual Local Application Support amount
-----------------------------------------------------------
Name 2 2004 Provincial direct 2 Company 300
Name 1 2004 Provincial direct 1 company 400
Total province-level transactions: 700
Name 3 2004 Shijiazhuang 3 company 200
Shijiazhuang total 200
2004 total 900
Name 3 2005 Shijiazhuang 3 company 100
Shijiazhuang total 100
2005 total 100
Total 1000

(The number of affected rows is 10)

--*/