求教一SQL语句

[topyyii]

表
原始数据：
编号 名称 人数
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语句该怎么编写呢？

[wacj940710057]

下面用游标解决多班总计时的问题：

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

[wacj940710057]

如需要可以 这样查询 （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行。

[wacj940710057]

修正一下 ：
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函数在“共计”前加上“一班”，“二班”等等的，没成功，却也忘了删掉，不好意思！）

[coolkisses]

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

[wacj940710057]

还有一个问题就是强烈希望楼主以后字段的命名多用 英文 ，少用（最好不要用中文），因为使用中文会造成很多潜在的问题

[wacj940710057]

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

[billlyh]

[Quote=引用 3 楼 的回复:]
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……
[/Quote]
学习

[啊彪123]

select * from score;
2110	 二一班 	5
2111	 二二班 	9
2112	 二三班	6
1111	b	4
1112	c	7
1110	a	2

select 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

[啊彪123]

其实要写存储过程取判断，做报表。

[ssqtjffcu]

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

[hupeng213]

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

[大明湖畔_帅锅]

[Quote=引用 3 楼 的回复:]

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) ……
[/Quote]

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

[hupeng213]

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

[啊彪123]

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

[wangpengpengwang]

高手很多啊！

[erey111111]

很好呀

[物润声无]

先mark一下，明天写，吃饭了~~

[wacj940710057]

[Quote=引用 14 楼 的回复:]
SQL code

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(……
[/Quote]

高手，值得学习

[物润声无]

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) <> 1

1110	一一班	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) 这句怎么解释？