Originally posted: http://community.9cbs.net/expert/topic/3317/3317231.xml? Temp = .7252313
The program is selected: the project name, project annual, land city, application unit, support amount, these four, the summary field is the project year, the city query results are required to follow the project name, project annual, land city, application unit, Support amount is arranged and the result can be generated according to the specified field: =================================================================== ====================================================================================================================================== 1 Company 1002 Name 2 2004 Provincial Straight 2 Company 1003 2004 Provincial Standard 2004 Name 3 2004 Shijiazhuang 3 Company 1005 2004 Shijiazhuang Integration 1006 2004 Total 3007 Name 3 2005 Shijiazhuang 3 Company 1008 2005 Shijiazhuang Integration 1009 2005 Total 10010 Total 400 ==== ============================================================================================================================================================================================================= ===========
-------------------------------------------------- ----------------------------------
- Suppose the following temporary table is obtained by the first summary sort processing (the first processing does not process small meter, total, etc.)
Create Table #t (Project Name VARCHAR (10), Project Annual VARCHAR (10), Gown Varchar (10), Application Unit VARCHAR (10), Supported INSERT #T SELECT 'Name 1', '2004', 'Provincial Straight', '1 Company', 400UNION All SELECT 'Name 2', '2004', 'Provisional', '2 Company', 300UNION All SELECT 'Name 3', '2004', 'Shijiazhuang', '3 Company ', 200UNION All SELECT' Name 3 ',' 2005 ',' Shijiazhuang ',' 3 Company ', 100GO - The following is to process the name of the SELECT project, the project year, the city, the application unit, support amount from (- this Is a summary part (not considering the summary of the total telometer) Select *, S1 = 0, S2 = project year, S3 = 0, S4 = land, S5 = 0, S6 = Application unit, S7 = 0 - This is from the ranked from #t union all - this is a summary of the small tab, total part of the SELECT project name = Case When Grouping (Project Annual) = 1 Tong 'total' else 'end, project annual = CASE When Grouping (Project Annual) = 0 And Grouping (City) = 1 THEN Project Annual 'Total' Else '' End, Land City = Case When Grouping (Project Annual) = 0 And Grouping (City) = 0 (Application Unit) = 1 THEN THE TISTER 'ELSE' END, Application Unit = Case When Grouping (Application Unit) = 0 THEN Application Unit Total 'Else' 'end, support amount = SUM (support amount) , Grouping (Project Annual), Project Annual - Here and below is the sort required, grouping (city), land, grouping (application unit), application unit, grouping (application unit) 1 from #t group by group by GROUP BY Project Annual, City, Application Unit With Rollup - This is to generate small, total packet fields) A ORDER BY S1, S2, S3, S4, S5, S6, S7, support amount - S1 ~ S6 is prevention Subtotal , The total sorting is required, the back of the support is the GO required for other sort required.
- Delete Temporary Temporary Table DROP TABLE #T
/*--Test Results
Project Name Project Annual City Application Unit Supported ------------------------------------ ------------ ----------- Name 1 2004 Provincial Straight 1 Company 400 1 Company Total 400 Name 2 2004 Straight 2 Company 300 2 Company Total 300 Provincial Litgen 700 Name 3 2004 Shijiazhuang 3 Company 200 3 Company Total 200 Shijiazhuang Integration 200 2004 Total 900 Name 3 2005 Shijiazhuang 3 Company 100 3 Company Total 100 Shijiazhuang Integration 100 2005 Total 100 Total 100 Total 1000 (The number of rows affected is 14 lines) - * /
- The top is the project year, the city, the application unit is tapped. The total - below is the year, the city is used, and the total, the total, more easily see the law - below is processing SELECT project name, project annual, land city, application unit, support amount from (- this is part of achieving summary (not considering the summary of the small tester) Select *, S1 = 0, S2 = project year, S3 = 0, S4 = land, S5 = 0 - This is the ranked from #t union all - this is a summary of the small count, total part of the SELECT project name = Case When Grouping (Project Annual) = 1 Then ' Total 'Else' 'end, project annual = Case When Grouping (Project Annual) = 0 And Grouping (City) = 1 THEN Project Annual ' Total 'Else' 'End, City = Case When GROUPING (City) = 0 THEN 'Total' Else '' end, '', support amount = SUM (support amount), grouping (project year), project year - here is the sort required, Grouping, GROUP, City, Grouping (City) 1 from #t group by Project Annual, City With Rollup - This is to generate small testers, total packet fields) A ORDER BY S1, S2, S3, S4, S5, support The amount - S1 ~ S6 is to prevent small measters, the total number of support is required, and the support amount behind is other sort required.
/*--Test Results
Project Name Project Annual City Application Unit Supported ------------------------------------ ----------------- Name 2 2004 Provincial Straight 2 Company 300 Name 1 2004 Provincial Straight 1 Company 400 Provincial Standard 700 Name 3 2004 Shijiazhuang 3 Company 200 Shijiazhuang Getter 2004 Total 900 Name 3 2005 Shijiazhuang 3 Company 100 Shijiazhuang Integration 100 2005 Total 100 Total 1000
(The number of rows affected is 10 lines)
- * /
