Oracle SQL查询语句统计数据的问题

Question

我想是想下面的效果，表和结果如下：
-- Create table
create table WL_RECEIPT
(
  S_ID     VARCHAR2(20) not null  primary key, 
  S_DEPTCODE VARCHAR2(20),
  S_DEPTNAME VARCHAR2(100),
  S_TEAMCODE     VARCHAR2(20),
  S_TEAMNNAME     VARCHAR2(100),
  i_price NUMBER(18,2)
)
               2010年度每月电量消耗情况统计表
制表人： 日期： 单位：万元
　 　 1月 2月 …… 12月 年度合计
车间1合计 　 　30 　60 　 　75 　165
　 小组1-1 　10 　15 　 　15 　40
　 小组1-2 　10 　15 　 　15 　40
　 小组1-3 　10 　30 　 　45 　85
车间2合计 　 　15 　45 　 　75 　135
　 小组2-1 　5 　15 　 　25 　45
　 小组2-2 　5 　15 　 　25 　45
　 小组2-3 　5 　15 　 　25 　45
消耗总计 　 　45 　105 　 　150 　300

Answer 1

SQL> select * from test;
 
HOUSE      TEAM       MONTHS YEARS COSTS
---------- ---------- ------ ----- -----
车间1      小组1-1         1  2010    10
车间1      小组1-1         2  2010    20
车间1      小组1-1         3  2010    30
车间1      小组1-1         4  2010    40
车间1      小组1-1         5  2010    50
车间1      小组1-1         6  2010    60
车间1      小组1-1         7  2010    70
车间1      小组1-1         8  2010    80
车间1      小组1-1         9  2010    90
车间1      小组1-1        10  2010   100
车间1      小组1-1        11  2010   110
车间1      小组1-1        12  2010   120
车间1      小组1-2         1  2010    11
车间1      小组1-2         2  2010    22
车间1      小组1-2         3  2010    33
车间1      小组1-2         4  2010    44
车间1      小组1-2         5  2010    55
车间1      小组1-2         6  2010    66
车间1      小组1-2         7  2010    77
车间1      小组1-2         8  2010    88
 
HOUSE      TEAM       MONTHS YEARS COSTS
---------- ---------- ------ ----- -----
车间1      小组1-2         9  2010    99
车间1      小组1-2        10  2010   110
车间1      小组1-2        11  2010   121
车间1      小组1-2        12  2010   132
车间1      小组1-3         1  2010    12
车间1      小组1-3         2  2010    24
车间1      小组1-3         3  2010    36
车间1      小组1-3         4  2010    48
车间1      小组1-3         5  2010    60
车间1      小组1-3         6  2010    72
车间1      小组1-3         7  2010    84
车间1      小组1-3         8  2010    96
车间1      小组1-3         9  2010   108
车间1      小组1-3        10  2010   120
车间1      小组1-3        11  2010   132
车间1      小组1-3        12  2010   144
车间2      小组2-3         1  2010    13
车间2      小组2-3         2  2010    26
车间2      小组2-3         3  2010    39
车间2      小组2-3         4  2010    52
车间2      小组2-3         5  2010    65
 
HOUSE      TEAM       MONTHS YEARS COSTS
---------- ---------- ------ ----- -----
车间2      小组2-3         6  2010    78
车间2      小组2-3         7  2010    91
车间2      小组2-3         8  2010   104
车间2      小组2-3         9  2010   117
车间2      小组2-3        10  2010   130
车间2      小组2-3        11  2010   143
车间2      小组2-3        12  2010   156
车间2      小组2-2         1  2010    14
车间2      小组2-2         2  2010    28
车间2      小组2-2         3  2010    42
车间2      小组2-2         4  2010    56
车间2      小组2-2         5  2010    70
车间2      小组2-2         6  2010    84
车间2      小组2-2         7  2010    98
车间2      小组2-2         8  2010   112
车间2      小组2-2         9  2010   126
车间2      小组2-2        10  2010   140
车间2      小组2-2        11  2010   154
车间2      小组2-2        12  2010   168
车间2      小组2-1         1  2010    14
车间2      小组2-1         2  2010    28
 
HOUSE      TEAM       MONTHS YEARS COSTS
---------- ---------- ------ ----- -----
车间2      小组2-1         3  2010    42
车间2      小组2-1         4  2010    56
车间2      小组2-1         5  2010    70
车间2      小组2-1         6  2010    84
车间2      小组2-1         7  2010    98
车间2      小组2-1         8  2010   112
车间2      小组2-1         9  2010   126
车间2      小组2-1        10  2010   140
车间2      小组2-1        11  2010   154
车间2      小组2-1        12  2010   168
 
72 rows selected
 
SQL> 
SQL> SELECT decode(team,NULL,house,team) t1,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13 FROM
  2  (SELECT house,team,SUM(decode(months,1,costs,0)) c1,SUM(decode(months,2,costs,0)) c2 ,SUM(decode(months,3,costs,0)) c3,SUM(decode(months,4,costs,0)) c4,SUM(decode(months,5,costs,0)) c5,SUM(decode(months,6,costs,0)) c6
  3             ,SUM(decode(months,7,costs,0)) c7 ,SUM(decode(months,8,costs,0)) c8,SUM(decode(months,9,costs,0)) c9,SUM(decode(months,10,costs,0)) c10,SUM(decode(months,11,costs,0)) c11,SUM(decode(months,12,costs,0)) c12
  4             ,SUM(costs) C13
  5    FROM test
  6   WHERE YEARs=2010
  7   GROUP BY house,team
  8   UNION ALL
  9  SELECT house||'合计',NULL team,SUM(decode(months,1,costs,0)),SUM(decode(months,2,costs,0)),SUM(decode(months,3,costs,0)),SUM(decode(months,4,costs,0)),SUM(decode(months,5,costs,0)),SUM(decode(months,6,costs,0))
 10             ,SUM(decode(months,7,costs,0)),SUM(decode(months,8,costs,0)),SUM(decode(months,9,costs,0)),SUM(decode(months,10,costs,0)),SUM(decode(months,11,costs,0)),SUM(decode(months,12,costs,0))
 11             ,SUM(costs)
 12    FROM test
 13   WHERE YEARs=2010
 14   GROUP BY house
 15   UNION ALL
 16   SELECT  NULL,'消耗总计',SUM(decode(months,1,costs,0)),SUM(decode(months,2,costs,0)),SUM(decode(months,3,costs,0)),SUM(decode(months,4,costs,0)),SUM(decode(months,5,costs,0)),SUM(decode(months,6,costs,0))
 17             ,SUM(decode(months,7,costs,0)),SUM(decode(months,8,costs,0)),SUM(decode(months,9,costs,0)),SUM(decode(months,10,costs,0)),SUM(decode(months,11,costs,0)),SUM(decode(months,12,costs,0))
 18             ,SUM(costs)
 19    FROM test
 20   WHERE YEARs=2010
 21  )  ORDER BY house DESC NULLS LAST,team ASC
 22  ;
 
T1                     C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12        C13
-------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
车间2合计              41         82        123        164        205        246        287        328        369        410        451        492       3198
小组2-1                14         28         42         56         70         84         98        112        126        140        154        168       1092
小组2-2                14         28         42         56         70         84         98        112        126        140        154        168       1092
小组2-3                13         26         39         52         65         78         91        104        117        130        143        156       1014
车间1合计              33         66         99        132        165        198        231        264        297        330        363        396       2574
小组1-1                10         20         30         40         50         60         70         80         90        100        110        120        780
小组1-2                11         22         33         44         55         66         77         88         99        110        121        132        858
小组1-3                12         24         36         48         60         72         84         96        108        120        132        144        936
消耗总计               74        148        222        296        370        444        518        592        666        740        814        888       5772
 
9 rows selected
 
SQL> 

Answer 2

用这个吧，还是有点差异，不过应该可以接受
SQL> SELECT decode(team,NULL,house,team) t1,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13 FROM
  2  (SELECT house,team,SUM(decode(months,1,costs,0)) c1,SUM(decode(months,2,costs,0)) c2 ,SUM(decode(months,3,costs,0)) c3,SUM(decode(months,4,costs,0)) c4,SUM(decode(months,5,costs,0)) c5,SUM(decode(months,6,costs,0)) c6
  3             ,SUM(decode(months,7,costs,0)) c7 ,SUM(decode(months,8,costs,0)) c8,SUM(decode(months,9,costs,0)) c9,SUM(decode(months,10,costs,0)) c10,SUM(decode(months,11,costs,0)) c11,SUM(decode(months,12,costs,0)) c12
  4             ,SUM(costs) C13
  5    FROM test
  6   WHERE YEARs=2010
  7   GROUP BY house,team
  8   UNION ALL
  9  SELECT house||'合计',NULL team,SUM(decode(months,1,costs,0)),SUM(decode(months,2,costs,0)),SUM(decode(months,3,costs,0)),SUM(decode(months,4,costs,0)),SUM(decode(months,5,costs,0)),SUM(decode(months,6,costs,0))
 10             ,SUM(decode(months,7,costs,0)),SUM(decode(months,8,costs,0)),SUM(decode(months,9,costs,0)),SUM(decode(months,10,costs,0)),SUM(decode(months,11,costs,0)),SUM(decode(months,12,costs,0))
 11             ,SUM(costs)
 12    FROM test
 13   WHERE YEARs=2010
 14   GROUP BY house
 15   UNION ALL
 16   SELECT  NULL,'消耗总计',SUM(decode(months,1,costs,0)),SUM(decode(months,2,costs,0)),SUM(decode(months,3,costs,0)),SUM(decode(months,4,costs,0)),SUM(decode(months,5,costs,0)),SUM(decode(months,6,costs,0))
 17             ,SUM(decode(months,7,costs,0)),SUM(decode(months,8,costs,0)),SUM(decode(months,9,costs,0)),SUM(decode(months,10,costs,0)),SUM(decode(months,11,costs,0)),SUM(decode(months,12,costs,0))
 18             ,SUM(costs)
 19    FROM test
 20   WHERE YEARs=2010
 21  )  ORDER BY house ASC NULLS LAST,team ASC ;
 
T1                     C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12        C13
-------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
小组1-1                10         20         30         40         50         60         70         80         90        100        110        120        780
小组1-2                11         22         33         44         55         66         77         88         99        110        121        132        858
小组1-3                12         24         36         48         60         72         84         96        108        120        132        144        936
车间1合计              33         66         99        132        165        198        231        264        297        330        363        396       2574
小组2-1                14         28         42         56         70         84         98        112        126        140        154        168       1092
小组2-2                14         28         42         56         70         84         98        112        126        140        154        168       1092
小组2-3                13         26         39         52         65         78         91        104        117        130        143        156       1014
车间2合计              41         82        123        164        205        246        287        328        369        410        451        492       3198
消耗总计               74        148        222        296        370        444        518        592        666        740        814        888       5772
 
9 rows selected
 
SQL> 

Answer 3

终于弄出来，排序可真麻烦
SQL> SELECT decode(team,NULL,house,team) t1,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13 FROM
  2  (SELECT house,team,SUM(decode(months,1,costs,0)) c1,SUM(decode(months,2,costs,0)) c2 ,SUM(decode(months,3,costs,0)) c3,SUM(decode(months,4,costs,0)) c4,SUM(decode(months,5,costs,0)) c5,SUM(decode(months,6,costs,0)) c6
  3             ,SUM(decode(months,7,costs,0)) c7 ,SUM(decode(months,8,costs,0)) c8,SUM(decode(months,9,costs,0)) c9,SUM(decode(months,10,costs,0)) c10,SUM(decode(months,11,costs,0)) c11,SUM(decode(months,12,costs,0)) c12
  4             ,SUM(costs) C13
  5    FROM test
  6   WHERE YEARs=2010
  7   GROUP BY house,team
  8   UNION ALL
  9  SELECT house||'合计',NULL team,SUM(decode(months,1,costs,0)),SUM(decode(months,2,costs,0)),SUM(decode(months,3,costs,0)),SUM(decode(months,4,costs,0)),SUM(decode(months,5,costs,0)),SUM(decode(months,6,costs,0))
 10             ,SUM(decode(months,7,costs,0)),SUM(decode(months,8,costs,0)),SUM(decode(months,9,costs,0)),SUM(decode(months,10,costs,0)),SUM(decode(months,11,costs,0)),SUM(decode(months,12,costs,0))
 11             ,SUM(costs)
 12    FROM test
 13   WHERE YEARs=2010
 14   GROUP BY house
 15   UNION ALL
 16   SELECT  NULL,'消耗总计',SUM(decode(months,1,costs,0)),SUM(decode(months,2,costs,0)),SUM(decode(months,3,costs,0)),SUM(decode(months,4,costs,0)),SUM(decode(months,5,costs,0)),SUM(decode(months,6,costs,0))
 17             ,SUM(decode(months,7,costs,0)),SUM(decode(months,8,costs,0)),SUM(decode(months,9,costs,0)),SUM(decode(months,10,costs,0)),SUM(decode(months,11,costs,0)),SUM(decode(months,12,costs,0))
 18             ,SUM(costs)
 19    FROM test
 20   WHERE YEARs=2010
 21  )  ORDER BY REPLACE(house,'合计','眼')||'组' ASC NULLS LAST,team ASC ;
 
T1                     C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11        C12        C13
-------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
车间1合计              33         66         99        132        165        198        231        264        297        330        363        396       2574
小组1-1                10         20         30         40         50         60         70         80         90        100        110        120        780
小组1-2                11         22         33         44         55         66         77         88         99        110        121        132        858
小组1-3                12         24         36         48         60         72         84         96        108        120        132        144        936
车间2合计              41         82        123        164        205        246        287        328        369        410        451        492       3198
小组2-1                14         28         42         56         70         84         98        112        126        140        154        168       1092
小组2-2                14         28         42         56         70         84         98        112        126        140        154        168       1092
小组2-3                13         26         39         52         65         78         91        104        117        130        143        156       1014
消耗总计               74        148        222        296        370        444        518        592        666        740        814        888       5772
 
9 rows selected
 
SQL>

Answer 4

以前用pb做报表的时候，用数据窗口里的交叉表一叉就可以了。
直接写sql就比较麻烦了