这个SQL语句怎么写???

Question

表有a,b,c三个字段如下:
  a   b   c
  1   11  10
  2   11  20
  3   22  30
  4   22  40
  
期望输出：
  
  a    b       c
  1    11      10
  2    11      20
       小计：  30
  3    22      30
  4    22      40
       小计：  70
       总计    100
  
脚本要如何写呢?

Answer 1

select a,decode(grouping_id(a,b),2,'小计',3,'总计',b),
sum(c)
from test_12
group by rollup(b,(a,b))

Answer 2

SQL> select a.*from test a;
 
A          B          C
---------- ---------- ----------
1          11         10
2          11         20
3          22         30
4          22         40
SQL> select a.*,sum(b)over(partition by b),sum(c)over(partition by b) from test a;
 
A          B          C          SUM(B)OVER(PARTITIONBYB) SUM(C)OVER(PARTITIONBYB)
---------- ---------- ---------- ------------------------ ------------------------
1          11         10                               22                       30
2          11         20                               22                       30
3          22         30                               44                       70
4          22         40                               44                       70楼主不知道这样行不？

Answer 3

SQL> select a.*,sum(c)over(partition by b),sum(c)over() from test a;
 
A          B          C          SUM(C)OVER(PARTITIONBYB) SUM(C)OVER()
---------- ---------- ---------- ------------------------ ------------
1          11         10                               30          100
2          11         20                               30          100
3          22         30                               70          100
4          22         40                               70          100看错了

Answer 4

grouping_id(a,b)
是什么意思啊

Answer 5

4.GROUPING_ID（）函数的使用GROUPING_ID（）函数可接受一列或多列，它返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是按照顺序对每一列调用GROUPING（）函数的结果组合起来。它的作用是借助HAVING子句对记录进行过滤，将不包含小计或总计的记录除去。GROUPING位向量的十进制值，我们由前面的介绍已知道当GROUPING（）的列值为空时它返回1，当非空时返回0；比如：division_id,job_id两列都为非空，GROUPING（）都返回0。将这两列的值组合起来，形成一个位向量00，十进制为0。即，当division_id,job_id都非空时，GROUPING_ID（）返回0。（这里要特别注意division_id与job_id两列的顺序）下面举个例子：select division_id,job_id, GROUPING(division_id) AS div_grp, GROUPING(job_id) AS job_grp, GROUPING_ID(division_id,job_id) AS grp_id,SUM(salary) from employees2  group by CUBE(division_id,job_id)  order by division_id; DIVISI JOB_ID    DIV_GRP    JOB_GRP     GRP_ID SUM(SALARY)------ ------ ---------- ---------- ---------- -----------BUS    MGR             0          0          0      530000BUS    PRE             0          0          0      800000BUS    WOR             0          0          0      280000BUS                    0          1          1     1610000OPE    ENG             0          0          0      245000OPE    MGR             0          0          0      805000OPE    WOR             0          0          0      270000OPE                    0          1          1     1320000SAL    MGR             0          0          0     4446000SAL    WOR             0          0          0      490000SAL                    0          1          1     4936000SUP    MGR             0          0          0      465000SUP    TEC             0          0          0      115000SUP    WOR             0          0          0      435000SUP                    0          1          1     1015000       ENG             1          0          2      245000       MGR             1          0          2     6246000       PRE             1          0          2      800000       TEC             1          0          2      115000       WOR             1          0          2     1475000                       1          1          3     8881000 已选择21行。下面是一个使用GROUPING_ID过滤不包含小计或总计的记录的例子：select division_id,job_id, GROUPING_ID(division_id,job_id) AS grp_id,SUM(salary) from employees2  group by CUBE(division_id,job_id)  having grouping_id(division_id,job_id)>0  order by division_id;DIVISI JOB_ID     GRP_ID SUM(SALARY)------ ------ ---------- -----------BUS                    1     1610000OPE                    1     1320000SAL                    1     4936000SUP                    1     1015000       ENG             2      245000       MGR             2     6246000       PRE             2      800000       TEC             2      115000       WOR             2     14750003               8881000================================================呵呵，这个我也不知道，学习学习

Answer 6

create table abc(
a  number,
b  number,
c  number
)
insert into abc values(1,11,10);
insert into abc values(2,11,20);
insert into abc values(3,22,30);
insert into abc values(4,22,40);SQL> select a,decode(grouping_id(a,b),2,'小计',3,'总计',b),
  2  sum(c)
  3  from abc
  4  group by rollup(b,(a,b))
  5  /         A DECODE(GROUPING_ID(A,B),2,'小?              SUM(C)
---------- ---------------------------------------- ----------
         1 11                                               10
         2 11                                               20
           小计                                             30
         3 22                                               30
         4 22                                               40
           小计                                             70
           总计                                            1007 rows selected

Answer 7

如果低版本的数据库,就用个UNION ALL吧，也没有什么问题
select a,b,c from t
union all
select '99', b, sum(c) from t group by b
order by b;
这样也简洁明了

Answer 8

select a,b,c from t 
union all 
select '99', b, sum(c) from t group by b 
union all
select '99' '合计', sum(c) from t;
order by b; 

Answer 9

select a,decode(grouping_id(a,b),2,'小计',3,'总计',b),
sum(c)
from test_12
group by rollup(b,(a,b))
;

Answer 10

group by b字段，
select *,sum(c) from table group by b字段语法不一定对，思想如此，多编译几次

Answer 11

嘿嘿就不和你写的一样SELECT A,DECODE(GROUPING_ID(B,A),1,'小计：',3,'总计：',B) B,SUM(C) C FROM 
TEST1 GROUP BY ROLLUP(B,A);