例如有一个table
格式为:
id pro_name cast
1 lass 100
1 tass 200
2 sass 100
3 mass 200
2 yass 100
1 uass 200
·····现在希望能够按照id进行统计,得到的结果是:
1 lass 100
1 tass 200
1 uass 200
1 500 --这一行是统计id = 1的所有行cast的合计
2 sass 100
2 yass 100
2 200 --这一行是统计id = 2的所有行cast的合计
3 mass 200
3 200 --这一行是统计id = 3的所有行cast的合计请问应该如何实现?
格式为:
id pro_name cast
1 lass 100
1 tass 200
2 sass 100
3 mass 200
2 yass 100
1 uass 200
·····现在希望能够按照id进行统计,得到的结果是:
1 lass 100
1 tass 200
1 uass 200
1 500 --这一行是统计id = 1的所有行cast的合计
2 sass 100
2 yass 100
2 200 --这一行是统计id = 2的所有行cast的合计
3 mass 200
3 200 --这一行是统计id = 3的所有行cast的合计请问应该如何实现?
解决方案 »
- 郁闷啊,查询结果集问题
- SQL2000中子查询和联接查询哪个好
- SQL 查询问题。怎么从表里只查询出一组里的一条数据
- 利用触发器技术实现双机备份
- oracle小问题,请高手帮忙!问题解决便可结贴!
- 进入了oracle9i的Enterprise Manager Console 用system登入了服务名datatest,可是备份是报出没有设置节点身份
- ???Long Raw 域内字符串的搜索问题???
- 欢迎加入oracle讨论群,绝对有效,群号1617315
- 哪里有oracle jdbc driver下?(oracle网站下不了)
- 当字段code=527时,写成两条数据,怎么写VIEW?
- 求指导,如果要查出2个字段都重复的数据,该如何写?
- 问下关于ORACLE 10g的版本问题
select id,pro_name,cast from tbl1
union all
select id,'' as pro_name,sum(cast) from tbl1
group by id
)
order by id,pro_name
group by rollup (id,pro_name)
group by id
select * from
(
select id,pro_name,cast from tbl1
union all
select id,'' "pro_name",sum(cast) "cast" from tbl1
group by id
)
order by id,cast
select id,pro_name,sum(cast) "cast" from table group by rollup (id,pro_name)
这个语句功能更强大,不光统计了每个id下的总和,还统计了所有记录的总和
(
ID NUMBER(4),
pro_name VARCHAR2(20),
CAST NUMBER(4)
);
INSERT INTO T81 VALUES(1, 'lass', 100);
INSERT INTO T81 VALUES(1, 'tass', 200);
INSERT INTO T81 VALUES(2, 'sass', 100);
INSERT INTO T81 VALUES(3, 'mass', 200);
INSERT INTO T81 VALUES(2, 'yass', 100);
INSERT INTO T81 VALUES(1, 'uass', 200);
实测结果:
但是有点儿画蛇添足了,楼主并没有要最后一行。select * from
(
select id,pro_name,cast from table
union all
select id,'',sum(cast) as cast from table group by id
)t
order by t.id,t.cast
AS
(
SELECT 1, 'lass', 100 FROM DUAL UNION
SELECT 1, 'tass', 200 FROM DUAL UNION
SELECT 2, 'sass', 100 FROM DUAL UNION
SELECT 3, 'mass', 200 FROM DUAL UNION
SELECT 2, 'yass', 100 FROM DUAL UNION
SELECT 1, 'uass', 200 FROM DUAL
)
SELECT ID,PRO_NAME,SUM(CAST)
FROM TB
GROUP BY ROLLUP(ID,PRO_NAME)
HAVING ID IS NOT NULL;
select id,sum(cast) from table
group by id
--创建测试表mytest
create table mytest(
id number,
pro_name varchar(20),
cast number
)
--插入数据
insert into mytest values(1,'lass',100)
insert into mytest values(1,'tass',200)
insert into mytest values(1,'sass',100)
insert into mytest values(1,'mass',200)
insert into mytest values(1,'yass',100)
insert into mytest values(1,'uass',200)--分两步查询
1、以id、pro_name分组统计
select id,pro_name,sum(cast)sum_cast from mytest group by id,pro_name order by id
结果如下:2、以id分组统计
select id,''pro_name,sum(cast)sum_cast from mytest group by id order by id
结果如下:--合并查询:
select a.* from(select id,pro_name,sum(cast)sum_cast from mytest group by id,pro_name)a
union all
select b.* from(select id,''pro_name,sum(cast)sum_cast from mytest group by id)b
--按id排序
select * from(
select a.* from(select id,pro_name,sum(cast)sum_cast from mytest group by id,pro_name)a
union all
select b.* from(select id,''pro_name,sum(cast)sum_cast from mytest group by id)b
) order by id,sum_cast查询结果如下:
上次图片有问题,再重发一次:--创建测试表mytest
create table mytest(
id number,
pro_name varchar(20),
cast number
)
--插入数据
insert into mytest values(1,'lass',100);
insert into mytest values(1,'tass',200);
insert into mytest values(1,'sass',100);
insert into mytest values(1,'mass',200);
insert into mytest values(1,'yass',100);
insert into mytest values(1,'uass',200);--分两步查询
1、以id、pro_name分组统计
select id,pro_name,sum(cast)sum_cast from mytest group by id,pro_name order by id
结果如下:
code][code=SQL]
2、以id分组统计
select id,''pro_name,sum(cast)sum_cast from mytest group by id order by id
结果如下:--将两条子句合并查询:
select a.* from(select id,pro_name,sum(cast)sum_cast from mytest group by id,pro_name)a
union all
select b.* from(select id,''pro_name,sum(cast)sum_cast from mytest group by id)b
--最终查询SQL,按id排序,
select * from(
select a.* from(select id,pro_name,sum(cast)sum_cast from mytest group by id,pro_name)a
union all
select b.* from(select id,''pro_name,sum(cast)sum_cast from mytest group by id)b
) order by id,sum_cast查询结果如下: