解决方案 »
- 求教:在linux下如何安装oracle 10g
- 分组筛选SQL语句求教
- 导出数据的时候query的参数如何处理?
- 安装Oracle9i怎么会出现这样的问题呢???大侠快看看啊!
- Oracle text manager
- this question is very lower, I need you help! Thanks!!数据库启动问题
- 关于导入/导出
- 数据文件已存在,怎么把它加到一个表空间上去?
- SQL*Plus 连服务器时出现ORA-12154:TNS:不能分解服务名称错误,别的机子却可以连上服务器,
- 如何判断一个表正在被某个用户使用
- 关于SQLP LUS客户端连接问题
- oracle自连接更新表内数据
select to_char(sysdate ,'yyyy/q') as quarter-- oracle求当年的第几季度
from dual
select yearno,qno,sum(amount) from (
select to_char(日期字段,'yyyy') as yearNo, to_char(日期字段,'1') as qNo,汇总字段 as amount from 表名
where 过滤条件
)
group by rollup(yearno,qno)
with test as(
select 50 amt, to_date('09-03-2011','dd-mm-yyyy') dat from dual
union all
select 40 amt, to_date('06-03-2011','dd-mm-yyyy') dat from dual
union all
select 30 amt, to_date('09-05-2011','dd-mm-yyyy') dat from dual
union all
select 20 amt, to_date('09-04-2011','dd-mm-yyyy') dat from dual
union all
select 10 amt, to_date('09-07-2011','dd-mm-yyyy') dat from dual
union all
select 60 amt, to_date('09-08-2011','dd-mm-yyyy') dat from dual
union all
select 80 amt, to_date('09-11-2011','dd-mm-yyyy') dat from dual
union all
select 70 amt, to_date('09-12-2011','dd-mm-yyyy') dat from dual
union all
select 55 amt, to_date('09-02-2012','dd-mm-yyyy') dat from dual
)
select amt, to_char(dat,'q') quater,to_char(dat,'yyyy') year
from test没想出来,把测试数据贴出来,等结果中~
select 50 amt, to_date('09-03-2011','dd-mm-yyyy') dat from dual
union all
select 40 amt, to_date('06-03-2011','dd-mm-yyyy') dat from dual
union all
select 30 amt, to_date('09-05-2011','dd-mm-yyyy') dat from dual
union all
select 20 amt, to_date('09-04-2011','dd-mm-yyyy') dat from dual
union all
select 10 amt, to_date('09-07-2011','dd-mm-yyyy') dat from dual
union all
select 60 amt, to_date('09-08-2011','dd-mm-yyyy') dat from dual
union all
select 80 amt, to_date('09-11-2011','dd-mm-yyyy') dat from dual
union all
select 70 amt, to_date('09-12-2011','dd-mm-yyyy') dat from dual
union all
select 55 amt, to_date('09-02-2012','dd-mm-yyyy') dat from dual
)
select quater, sum(amt)
from (
select amt, to_char(dat,'q') quater,to_char(dat,'yyyy') year
from test)
group by rollup(year, quater)
having quater is not null
order by year1 90
2 50
3 70
4 150
1 55