解决方案 »
- 为什么oracle的时间格式没修改过来呢?
- 如何在oracle数据库表的字段名定义中建立公式?
- 在线求一查询语句?很急
- 关于ORACLE数据库的备份和回复
- oracle里如何动态执行字符串,如'2*8*0.2'
- oracle11g exp导出问题
- 遇到过好几次这样的需求,请教
- redflag下oracle数据库误删除数据文件换位控制文件后如何及时恢复????????
- 谁有VI编辑器的详细命令列表
- dba高手:请问Oracle Migration Workbanch(sql7.0插件)进行数据移植时,出现 “fialed to load source model.数据大小超出此类型的最大
- oracle developer连接oracle数据库出现the network adapter cannot establish the connection
- oracle在linux下安装
select '北京' city,'cd' product,2000 year,200 sales from dual
union all
select '北京' city,'dvd' product,2001 year,300 sales from dual
union all
select '北京' city,'tv' product,2001 year,300 sales from dual
union all
select '上海' city,'dvd' product,1999 year,300 sales from dual
union all
select '上海' city,'tv' product,1999 year,100 sales from dual
union all
select '上海' city,'cd' product,1999 year,100 sales from dual
union all
select '上海' city,'dvd' product,2000 year,200 sales from dual
)
select city,product,sales from (
select city,product,year,sales,'a' sorts from temp
union all
select city,null product,null year,sum(sales) sales,'b' sorts from temp group by rollup(city)
) order by city,sorts
select '北京' city,'cd' product,2000 year,200 sales from dual
union all
select '北京' city,'dvd' product,2001 year,300 sales from dual
union all
select '北京' city,'tv' product,2001 year,300 sales from dual
union all
select '上海' city,'dvd' product,1999 year,300 sales from dual
union all
select '上海' city,'tv' product,1999 year,100 sales from dual
union all
select '上海' city,'cd' product,1999 year,100 sales from dual
union all
select '上海' city,'dvd' product,2000 year,200 sales from dual
)
select city,product,sales from (
select city,product,year,sales,'a' sorts from temp
union all
select city,null product,null year,sum(sales) sales,'b' sorts from temp group by rollup(city)
) where city is not null order by city,sorts
这样是可以可使,年就不会再显示了,我用下面也可以实现这样的效果,可使年也是不显示了
select city,product,sum(sales) from tmp1 group by city, rollup(product) order by city
select city,product,year,sales from (
select city,product,year,sales,'a' sorts from temp
union all
select city,null product,null year,sum(sales) sales,'b' sorts from temp group by rollup(city)
) where city is not null order by city,sorts
select '北京' city,'cd' product,2000 year,200 sales from dual
union all
select '北京' city,'dvd' product,2001 year,300 sales from dual
union all
select '北京' city,'tv' product,2001 year,300 sales from dual
union all
select '上海' city,'dvd' product,1999 year,300 sales from dual
union all
select '上海' city,'tv' product,1999 year,100 sales from dual
union all
select '上海' city,'cd' product,1999 year,100 sales from dual
union all
select '上海' city,'dvd' product,2000 year,200 sales from dual
)
select city,product,year,sum(sales)
from temp
group by city,rollup ((product,year))
请问最后((product,year))这个怎么理解?
CITY PRODUCT YEAR SUM(SALES)
上海 cd 1999 100
上海 cd NULL 100
上海 tv 1999 100
上海 tv NULL 100
上海 dvd 2000 200
上海 dvd 1999 300
上海 dvd NULL 500
上海 NULL NULL 700
北京 cd 2000 200
北京 cd NUL 200
北京 tv 2001 300
北京 tv NULL 300
北京 dvd 2001 300
北京 dvd NULL 300
北京 NULL NULL 800