日期:2014-05-17 浏览次数:20888 次
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
------解决方案--------------------
--创建测试表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]