oracle某一字段分组求和,关于对某列进行分组求和的有关问题

SQL code--创建测试表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

结果如下：

[img=http://b151.photo.store.qq.com/psb?/b1320946-81af-4671-9124-ef998a845dfb/p0Zmwitb3NTgDt62ic6X15nw4n6SUj.ArBY5DJB16hs!/b/YT59C1qMRQAAYvgGDVqGSgAA][/img]

--合并查询：

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

查询结果如下：

[img=http://b203.photo.store.qq.com/psb?/b1320946-81af-4671-9124-ef998a845dfb/D91LDiDJ9cDtM**O6XF3WPDwDUVHbB8kRPtniiq3gl8!/b/YX0hEHkesQAAYn0hEHkdsQAA][/img]