求教一SQL语句

Question

表
原始数据：
编号        名称    人数
 1110       一一班  6
 1111       一二班  5
 1112       一三班  5
 
 2110       二一班  5
 2111       二二班  9
 2112       二三班  6
我想按照年级  也就是编号的第一位  统计每个年级总人数  并插入到该表
结果：
编号        名称    人数
 1110       一一班  6
 1111       一二班  5
 1112       一三班  5
 1999       总计    16 2110       二一班  5
 2111       二二班  9
 2112       二三班  6
 2999       总计    20
sql语句该怎么编写呢？

Answer 1

select * from score;
2110  二一班  5
2111  二二班  9
2112  二三班 6
1111 b 4
1112 c 7
1110 a 2select to_number(substr(to_char(num),0,1)||999) num,'总计' name,sum(count) count 
from score group by substr(to_char(num),0,1) 
union all select * from score;1 1999 总计 13
2 2999 总计 20
3 2110  二一班  5
4 2111  二二班  9
5 2112  二三班 6
6 1111 b 4
7 1112 c 7
8 1110 a 2

Answer 2

SELECT cname,
  quantity
FROM
  (SELECT DECODE(cname,NULL,'编号'
    || SUBSTR(code,1,1)
    || '小计',cname) AS cname,
    grouping(cname) a,
    grouping(SUBSTR(code,1,1)) b,
    SUM(quantity) AS quantity
  FROM
    ( SELECT '1110' AS code, '一一班' AS cname, 6 AS quantity FROM dual
    UNION ALL
    SELECT '1111', '一二班', 5 FROM dual
    UNION ALL
    SELECT '1112', '一三班', 5 FROM dual
    UNION ALL
    SELECT '2110', '二一班', 5 FROM dual
    UNION ALL
    SELECT '2111', '二二班', 9 FROM dual
    UNION ALL
    SELECT '2112', '二三班', 6 FROM dual
    )
  GROUP BY rollup(SUBSTR(code,1,1),cname)
  )
WHERE NOT (a=1
AND b       =1)--结果
CNAME      QUANTITY               
---------- ---------------------- 
一二班     5                      
一三班     5                      
一一班     6                      
编号1小计   16                     
二二班     9                      
二三班     6                      
二一班     5                      
编号2小计   20                     
 8 rows selected 

Answer 3

  还是没想到这上面去   还在想着怎么嵌套GROUP BY rollup来实现分组汇总 

Answer 4

上面还可以改进，在GROUP BY 子句后面加上having判断，最外层那层查询都可以不用。

Answer 5

select substr(to_char(num),0,1), NVL(name,'合计')||substr(to_char(num),0,1),sum(count) count
from score group by rollup(substr(to_char(num),0,1),name);1 a1 2
1 b1 4
1 c1 7
1 合计1 13
2  二三班2 6
2  二二班 2 9
2  二一班 2 5
2 合计2 20
合计 33

Answer 6

下面用游标解决多班总计时的问题：  DECLARE
    CURSOR C_1 IS
      SELECT * FROM TEST1 T; --定义一个游标
    R C_1%ROWTYPE; --定义一个行对象，用于获得游标的值
    temp NUMBER(10)  :=0;
    SUM_NUM NUMBER(6) :=0;
  BEGIN
    IF C_1%ISOPEN THEN
      CLOSE C_1;
    END IF;
    OPEN C_1;   
    LOOP   
      FETCH C_1
        INTO R; --取值
      IF SUBSTR(TEMP, 1, 1) = SUBSTR(R.编号,1,1) THEN
       GOTO A;   --判断当前行是否是同一个班的，是则跳过下面的语句继续循环
      END IF;
      TEMP := R.编号;
      SELECT SUM(人数) INTO SUM_NUM FROM test1 WHERE 编号 LIKE substr(R.编号,1,1)||'%';   
      INSERT INTO TEST1 (编号, 名称, 人数) VALUES (substr(R.编号,1,1)||999, decode('总计', SUM_NUM);
      <<A>>
      EXIT WHEN C_1%NOTFOUND; --如果游标没有取到值，退出循环.
    END LOOP;
  END;结果如下：
select * from test1;       编号 名称          人数
----------- ---------- -------
       1110 一一班           6
       1111 一二班           5
       1112 一三班           5
       2110 二一班           5
       2111 二二班           9
       2112 二三班           6
       3110 三一班           8
       3111 三二班          10
       3112 三三班           6
       3113 三四班           7
       4110 四一班           9
       4111 四二班           7
       4112 四三班           6
       4113 四四班           9
       5110 五一班           6
       5111 五二班          12
       5112 五三班           4
       5113 五四班           8
       1999 总计            16
       2999 总计            20
       3999 总计            31
       4999 总计            31
       5999 总计            30

Answer 7

如需要可以 这样查询 （select * from test1 order by 编号;） 即可
结果如下：
SQL> select * from test1 order by 编号;      编号 名称             人数
---------- ---------- ----------
      1110 一一班              6
      1111 一二班              5
      1112 一三班              5
      1999 总计               16
      2110 二一班              5
      2111 二二班              9
      2112 二三班              6
      2999 总计               20
      3110 三一班              8
      3111 三二班             10
      3112 三三班              6
      3113 三四班              7
      3999 总计               31
      4110 四一班              9
      4111 四二班              7
      4112 四三班              6
      4113 四四班              9
      4999 总计               31
      5110 五一班              6
      5111 五二班             12
      5112 五三班              4
      5113 五四班              8
      5999 总计               30已选择23行。

Answer 8

修正一下 ：
 SELECT SUM(人数) INTO SUM_NUM FROM test1 WHERE 编号 LIKE substr(R.编号,1,1)||'%';   
  INSERT INTO TEST1 (编号, 名称, 人数) VALUES (substr(R.编号,1,1)||999, decode('总计', SUM_NUM);
  <<A>>
  EXIT WHEN C_1%NOTFOUND; --如果游标没有取到值，退出循环.
  END LOOP;
其中红色删掉即可（注：本是想用decode函数在“共计”前加上“一班”，“二班”等等的，没成功，却也忘了删掉，不好意思！）

Answer 9

只是一条sql就可搞定，为什么越来越复杂？

Answer 10

10楼，你好，如果班级很多的话，如何用一跳sql

Answer 11

create table student(
bh varchar2(10),
mc varchar2(10),
rs int)
;
insert into student values('1110','一一班',6);
insert into student values('1111','一二班',5);
insert into student values('1112','一三班',5);
insert into student values('2110','二一班',5);
insert into student values('2111','二二班',9);
insert into student values('2112','二三班',6);
commit;
insert into student
  select substr(bh, 1, 1) || '999', '总数', sum(rs)
    from student
   group by substr(bh, 1, 1);
commit;SQL> select * from student order by bh;
 
BH         MC                                              RS
---------- ---------- ---------------------------------------
1110       一一班                                           6
1111       一二班                                           5
1112       一三班                                           5
1999       总数                                            16
2110       二一班                                           5
2111       二二班                                           9
2112       二三班                                           6
2999       总数                                            20

Answer 12

with test as(
select '1110' id,'一一班' name, 6 num from dual
union all
select '1111', '一二班', 5 from dual
union all
select '1112','一三班',5 from dual
union all
select '2110','二一班', 5 from dual
union all
select '2111', '二二班', 9 from dual
union all
select '2112','二三班',6 from dual
)
select  decode(id,null,rpad(substr(id,1,1),4,'9'),id), decode(name,null,(decode(substr(id,1,1),'1','一班小记','2','二班小记')),name), sum(num)
from test
group by rollup(substr(id,1,1),id, name)
having grouping_id(id, name) <> 11110 一一班 6
1111 一二班 5
1112 一三班 5
1999 一班小记 16
2110 二一班 5
2111 二二班 9
2112 二三班 6
2999 二班小记 20
(null) (null) 36只能想到上面的结果了，
怎么把最后那个总计给去掉啊？
还有：decode(id,null,rpad(substr(id,1,1),4,'9'),id) 这句怎么解释？