From: http://community.9cbs.net/expert/topic/3372/3372925.xml? Temp = .7849237
--test:
Create Table JS (Year INT, Month, Precipitation Float) Insert Into Jsselect 1971, 1,34.5 Unionlect 1971, 2,23 UnionSelect 1972, 1,8.5 UnionSelect 1972, 2, 23 Unionlect 1972, 2, 23 Unionlect 1972, 3, 12.0 Union SELECT 1973, 3, 122.5
- Generate a few intermediate table Select b. * INTO # t1from (select b. Month, precipitation = max (b. Precipitation) from JS A, JS bwhere a. Precipitation> = b. Precipitation and a. Month = B. Month GROUP B. Month) A inner Join Js B on a. Precipitation = B. Precipitation Order by b. Month
SELECT year, total precipitation = Sum (precipitation) INTO # T2From jsgroup by year
Select # t1. *, # t2. Total precipitation INTO # T1 inner Join # T2 on # T1. year = # t2. year
- Query SELECT Convert (VARCHAR (10), Years) AS [Year / Month], [1] = Convert (VARCHAR, SUM (Case Moon When 1 Then Precipitation End), [2] = Convert (Varchar, SUM Case Moon When 2 Then Precipitation End)), [3] = Convert (VARCHAR, SUM (Case Moon When 3 Ten Precipitation End), [4] = Convert (Varchar, Sum (Case Moon When 4 Then Prefare End) [5] = Convert (VARCHAR, SUM (Case Moon When 5 Then Precipitation End), [6] = Convert (VARCHAR, SUM (Case Moon When 6 Then Precipitation End), [7] = Convert (Varchar, SUM Case Moon When 7 Then Precipitation End), [8] = Convert (VARCHAR, SUM (Case Moon When 8 Ten Prefare End), [9] = Convert (Varchar, Sum (Case Moon When 9 Ten Precipitation End), [10] = Convert (VARCHAR, SUM (Case Moon When 10 Then Precipitation End), [11] = Convert (VARCHAR, SUM (Case Moon When 11 Then Precipitation End), [12] = Convert (Varchar, SUM Case Moon When 12 Ten Precipitation End)), Total = Convert (VARCHAR, SUM (Precipitation)) from JS - Here is the Name Group By Nion All Select 'Average' AS 'Year / Month', [1] = Convert (VARCHAR, CONVERT (DEC (10, 1), AVG (Case Moon When 1 Then Precipitation End))) End))))))), [3] = Convert (VARCHAR, CONVERT (DEC 10, 1), AVG (Case Moon When 3 Then Precipitation End))))), [4] = Convert (Varchar, Convert (DEC (10, 1), AVG (Case Moon When 4 Then Precipitation End)), [5) ] = Convert (VARCHAR, CONVERT (DEC (10, 1), AVG (Case Moon When 5 Then Precipitation End)), [6] = Convert (VARCHAR, Convert (DEC (10, 1), AVG (Case Moon When) 6 THEN precipitation end)))), [7] = Convert (VARCHAR, CONVERT (DEC (10, 1), AVG (Case Moon When 7 Then Precipitation End))), [8] = Convert (VARCHAR, Convert (DEC 10, 1), AVG (Case Moon When 8 Ten Precipitation End)))), [9] = Convert (VARCHAR, CONVERT (DEC (10, 1), AVG (Case Moon When 9 Then Precipitation End)), [10 ] = Convert (VARCHAR, CONVERT (DEC (10, 1), AVG (Case Moon When 10 Then Precipitation End)), [11] =
Convert (VARCHAR, CONVERT (DEC (10, 1), AVG (Case Moon When 11 Then Precipitation End))), [12] = Convert (VARCHAR, Convert (DEC (10, 1), AVG (Case Moon When 12 Then) Precipitation End)) Years / month ', [1] = Convert (VARCHAR, MAX (Case Moon When 1 Then Precipitation End), [2] = Convert (VARCHAR, MAX (Case Moon When 2 Then Precipitation End), [3] = Convert (VARCHAR, MAX (Case Moon When 3 Ten Precipitation End), [4] = Convert (VARCHAR, MAX (Case Moon When 4 Then Precipitation End), [5] = Convert (Varchar, Max (Case Moon When 5 Then Precipitation end)), [6] = Convert (VARCHAR, MAX (Case Moon When 6 Ten Prefare End), [7] = Convert (VARCHAR, MAX (Case Moon When 7 Ten Prefare End), [8] = Convert (VARCHAR, MAX (Case Moon)), [9] = Convert (Varchar, Max (Case Moon When 9 Ten Prefare End), [10] = Convert (VARCHAR, MAX (Case Moon When 10 THEN) Precipitation End)), [11] = Convert (VARCHAR, MAX (Case Moon When 11 Then Precipitation End), [12] = Convert (VARCHAR, MAX (Case Moon When 12 Then Precipitation End), total = Convert (varchar, (SELECT SUM (SUM, Maximum Precipitation = Max (Precipitation) from JS Group By) A) "From Jsunion AllSelect 'Year / month' = 'Announced', [1] = Convert (VARCHAR (4), Max (Case Moon When 1 THEN END)), [2] = Convert (VARCHAR (4), Max (Case Moon When 2 Then Year End), [3] = Convert (varchar (VARCHAR) 4), Max (Case Moon When 3 THEN End), [4] = Convert (VARCHAR (4), Max (Case Moon When 4 THEN End), [5] = Convert (Varchar (4), Max (Case Moon When 5 THEN END)), [6] = Convert (VARCHAR (4), Max (Case Moon When 6 THEN End), [7] = Convert (Varchar (4), Max (Case Moon When) 7 THEN END)), [8] = Convert (VARCHAR (4), Max (Case Moon When 8 Then End), [9] =
Convert (VARCHAR (4), Max (Case Moon When 9 Then End), [10] = Convert (Varchar (4), Max (Case Moon When 10 Then End), [11] = Convert (varchar 4), Max (Case Moon When 11 THEN END), [12] = Convert (varchar (4), Max (Case Moon When 12 THEN End), total = Convert (varchar, (Select Top 1 B). Year from (SELECT year = max (b. Year), total precipitation = max (b. Total precipitation) from # TA, # TB where a. Total precipitation> = b. Total precipitation and a. Year = b. Year) a Inner Join #tb on a. total precipitation = b. total precipitation)) from #t b - Delete Test Table Drop Table JS, # T, # T1, # T2
- Operation: Year / month 1 2 3 4 ..... More 34.5 23.5 56 NULL ..... 113.51972 8.5 33 12 NULL ..... 53.5 Average 21.5 28.3 34.0 NULL ..... 83.75 Maximum value 34.5 33 56 NULL ..... 123.5 Appearance 1971 1972 1971 NULL ... 1971
