求一句sql

Question

表T select * from T 结果如下
col1   col2
 1      3
 1      1
 2      7
 2      4
 2      1
 3      4
 3      3希望可以通过一句sql实现针对col1分组显示明细信息后显示合计信息，如下：
col1   col2
 1      3
 1      1
'小计'  4
 2      7
 2      4
 2      1
'小计'  12
 3      4
 3      3
'小计'  7
可以实现吗？谢谢

Answer 1

提示：select sun(decode('列名'，‘如果列是辞职显示’，‘如果列是辞职显示’)) 

Answer 2

with tt as 
(
select 1 col1, 3 col2 from dual
union all
select 1,1 from dual
union all
select 2,7 from dual
union all
select 2,4 from dual
union all
select 2,1 from dual
union all
select 3,4 from dual
union all
select 3,2 from dual
)
select col1,col2,sum(col2)over(partition by col1 order by col1) 小计 from tt t;
      COL1       COL2       小计
---------- ---------- ----------
         1          3          4
         1          1          4
         2          7         12
         2          4         12
         2          1         12
         3          4          6
         3          2          67 rows selected.
楼主的哪种写法有空在研究下...
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群：62697716 

Answer 3

SQL> with tt as
  2  (
  3  select 1 col1, 3 col2 from dual
  4  union all
  5  select 1,1 from dual
  6  union all
  7  select 2,7 from dual
  8  union all
  9  select 2,4 from dual
 10  union all
 11  select 2,1 from dual
 12  union all
 13  select 3,4 from dual
 14  union all
 15  select 3,2 from dual
 16  )
 17  select decode(grouping_id (col1, col2),1,'小计',3,'总计',0,col1) new_col1, sum(col2) new_col2
 18    from tt
 19   group by rollup(col1,col2);
 
NEW_COL1                                   NEW_COL2
---------------------------------------- ----------
1                                                 1
1                                                 3
小计                                              4
2                                                 1
2                                                 4
2                                                 7
小计                                             12
3                                                 2
3                                                 4
小计                                              6
总计                                             22
 
11 rows selected
 
Executed in 0.109 seconds

Answer 4

我都说了不行了，不要总计，有总计。况且你按col1,col2分组的话，如果有重复的数据那就会合并，这也是问题啊

Answer 5

select col1, col2 from (
select to_char(col1) as col1, col1 as group_kind,col2 from T
union all
select '小计' as col1, col1 as group_kind ,sum(col2) as col2
from T
group by col1)
order by group_kind, col1;得出的结果
col1   col2
1 1
1 3
小计 4
2 4
2 7
2 1
小计 12
3 3
3 4
小计 7

Answer 6

SQL> select * from t;      COL1       COL2
---------- ----------
         1          3
         1          1
         2          7
         2          4
         2          1
         3          4
         3          37 rows selected.SQL> select col1,col2
  2  from (
  3  select to_char(col1) as col1,col2,col1 as k,0 as k2 from t
  4  union all
  5  select '小计',sum(col2),col1 as k,1 as k2 from t group by col1
  6  )
  7  order by k,k2;COL1                                           COL2
---------------------------------------- ----------
1                                                 1
1                                                 3
小计                                              4
2                                                 4
2                                                 7
2                                                 1
小计                                             12
3                                                 3
3                                                 4
小计                                              710 rows selected.SQL>

Answer 7

--帮你解决问题不是求你，以后把重复记录和不需总计等情况说清楚一些：
SQL> with tt as
  2  (
  3  select 1 col1, 3 col2 from dual
  4  union all
  5  select 1,1 from dual
  6  union all
  7  select 1,1 from dual
  8  union all
  9  select 2,7 from dual
 10  union all
 11  select 2,4 from dual
 12  union all
 13  select 2,1 from dual
 14  union all
 15  select 3,4 from dual
 16  union all
 17  select 3,2 from dual
 18  )
 19  select decode(grouping_id (col1, col2),1,'小计',3,'总计',0,col1) new_col1, sum(distinct col2) new_col2
 20    from tt
 21   group by rollup(col1,col2)
 22  having grouping_id (col1, col2) <> 3
 23   order by col1, col2;
 
NEW_COL1                                   NEW_COL2
---------------------------------------- ----------
1                                                 1
1                                                 3
小计                                              4
2                                                 1
2                                                 4
2                                                 7
小计                                             12
3                                                 2
3                                                 4
小计                                              6
 
10 rows selected
 
Executed in 0.078 seconds

Answer 8

可以用Oracle的rollup这个函数
SELECT CASE
         WHEN GROUPING(T.COL2) = 1 THEN
          '小计'
         ELSE
          to_char(T.COL2)
       END AS COL1,
       SUM(T.COL2) AS SUM_COL2
  FROM T T
 GROUP BY T.COL1,ROLLUP(T.COL2)
 ORDER BY T.COL1

Answer 9

还是用rollup来实现吧。
select decode(grouping(rownum),1,'小计',to_char(col1)) col1,sum(col2) col2
from t
group by col1,rollup(rownum) order by t.col1;

Answer 10

11:22:24 tina@PRACTICE> select * from t;      COL1       COL2
---------- ----------
         1          3
         1          1
         2          7
         2          4
         2          1
         3          4
         3          3
         3          7
         3          7已选择9行。已用时间:  00: 00: 00.00
11:22:27 tina@PRACTICE> select decode(grouping(rownum),1,'小计',to_char(col1)) col1,sum(col2) col2
11:22:46   2  from t
11:22:46   3  group by col1,rollup(rownum) order by t.col1;COL1                                           COL2
---------------------------------------- ----------
1                                                 3
1                                                 1
小计                                              4
2                                                 7
2                                                 4
2                                                 1
小计                                             12
3                                                 4
3                                                 3
3                                                 7
3                                                 7
小计                                             21已选择12行。已用时间:  00: 00: 00.01

Answer 11

谢谢各位：GROUP BY T.COL1,ROLLUP(T.COL2) 这个方法对路。
to mantisXF：请看看上面的方法，你也别求我了。结贴