例如有一个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的合计
请问应该如何实现？
------解决方案--------------------
select id,pro_name,cast from (
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
------解决方案--------------------
- SQL code
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
------解决方案--------------------
- SQL code
--创建测试表mytestcreate 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)aunion allselect 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)aunion allselect 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]