oracle 1月-12月数据查询

StringBuilder sql = new StringBuilder();

sql.append("select ");

sql.append("(");

sql.append(" select count(*) from fds_case t where ");

sql.append(" t.belongsystemunid = '28B78C2F0E9ECBA275775794C1058F2A' ");

sql.append(" and (t.occur_time between '2013-01-01' and to_char(last_day(date'2013-01-01'),'YYYY-MM-DD')) ");

sql.append(") as 一月份,");

sql.append("(");

sql.append(" select count(*) from fds_case t where ");

sql.append(" t.belongsystemunid = '28B78C2F0E9ECBA275775794C1058F2A'");

sql.append(" and (t.occur_time between '2013-02-01' and to_char(last_day(date'2013-02-01'),'YYYY-MM-DD')) ");

sql.append(") as 二月份,");

sql.append(" from dual;");

select substr(t.create_time, 0, 4) years,

sum(decode(substr(t.create_time, 6, 2), '01', 1, 0)) 月01,

sum(decode(substr(t.create_time, 6, 2), '02', 1, 0)) 月02,

sum(decode(substr(t.create_time, 6, 2), '03', 1, 0)) 月03,

sum(decode(substr(t.create_time, 6, 2), '04', 1, 0)) 月04,

sum(decode(substr(t.create_time, 6, 2), '05', 1, 0)) 月05,

sum(decode(substr(t.create_time, 6, 2), '06', 1, 0)) 月06,

sum(decode(substr(t.create_time, 6, 2), '07', 1, 0)) 月07,

sum(decode(substr(t.create_time, 6, 2), '08', 1, 0)) 月08,

sum(decode(substr(t.create_time, 6, 2), '09', 1, 0)) 月09,

sum(decode(substr(t.create_time, 6, 2), '10', 1, 0)) 月10,

sum(decode(substr(t.create_time, 6, 2), '11', 1, 0)) 月11,

sum(decode(substr(t.create_time, 6, 2), '12', 1, 0)) 月12,

count(1) alls

from FDS_CASE t

where t.del = 'N'

and t.belongsystemunid = '28B78C2F0E9ECBA275775794C1058F2A'

欢迎大家访问我的个人网站萌萌的IT人

posted on 2013-03-19 17:35 一堣而安阅读(1622) 评论(0) 编辑收藏所属分类: oracle


只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻 Chat2DB C++博客博问管理
相关文章: ORA-12154 win7_oracle11g_64位连接32位PLSQL_Developer oracle导出表结构的几种方法。 oracle在导入时指定不导入某张表手动重建em资料库 oracle批量插入 ORACLE SEQUENCE用法 left join加上where条件的困惑 ORA-00904标识符无效 oracle的nvl和sql server的isnull （转）

tinguo002