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]