设定你的表名为T
SELECT B.*
FROM (
SELECT 'A' AS 分类 FROM DUAL UNION ALL
SELECT 'B' AS 分类 FROM DUAL UNION ALL
SELECT 'C' AS 分类 FROM DUAL UNION ALL
SELECT 'D' AS 分类 FROM DUAL UNION ALL
SELECT 'E' AS 分类 FROM DUAL
)A,(
SELECT 分类,count(1) 记录总数,
sum(decode(to_char(创建时间,'mm'),'01',1)) "1月",
sum(decode(to_char(创建时间,'mm'),'02',1)) "2月",
sum(decode(to_char(创建时间,'mm'),'03',1)) "3月",
sum(decode(to_char(创建时间,'mm'),'04',1)) "4月",
sum(decode(to_char(创建时间,'mm'),'05',1)) "5月",
sum(decode(to_char(创建时间,'mm'),'06',1)) "6月",
sum(decode(to_char(创建时间,'mm'),'07',1)) "7月",
sum(decode(to_char(创建时间,'mm'),'08',1)) "8月",
sum(decode(to_char(创建时间,'mm'),'09',1)) "9月",
sum(decode(to_char(创建时间,'mm'),'10',1)) "10月",
sum(decode(to_char(创建时间,'mm'),'11',1)) "11月",
sum(decode(to_char(创建时间,'mm'),'12',1)) "12月"
FROM T
where to_char(创建时间,'yyyy')='2014'
group by 分类
)B
WHERE A.分类(+)=B.分类
order by A.分类
SELECT B.*
FROM (
SELECT 'A' AS 分类 FROM DUAL UNION ALL
SELECT 'B' AS 分类 FROM DUAL UNION ALL
SELECT 'C' AS 分类 FROM DUAL UNION ALL
SELECT 'D' AS 分类 FROM DUAL UNION ALL
SELECT 'E' AS 分类 FROM DUAL
)A,(
SELECT 分类,count(1) 记录总数,
sum(decode(to_char(创建时间,'mm'),'01',1)) "1月",
sum(decode(to_char(创建时间,'mm'),'02',1)) "2月",
sum(decode(to_char(创建时间,'mm'),'03',1)) "3月",
sum(decode(to_char(创建时间,'mm'),'04',1)) "4月",
sum(decode(to_char(创建时间,'mm'),'05',1)) "5月",
sum(decode(to_char(创建时间,'mm'),'06',1)) "6月",
sum(decode(to_char(创建时间,'mm'),'07',1)) "7月",
sum(decode(to_char(创建时间,'mm'),'08',1)) "8月",
sum(decode(to_char(创建时间,'mm'),'09',1)) "9月",
sum(decode(to_char(创建时间,'mm'),'10',1)) "10月",
sum(decode(to_char(创建时间,'mm'),'11',1)) "11月",
sum(decode(to_char(创建时间,'mm'),'12',1)) "12月"
FROM T
where to_char(创建时间,'yyyy')='2014'
group by 分类
)B
WHERE A.分类(+)=B.分类
order by A.分类
解决方案 »
- orcale ORA-04098: 触发器 'SCOTT.TEST_PK'
- 照网上学的简单例子提示出错? 调用存储过程游标返回结果集的问题!!!
- oracle 10g中表数据莫名其妙丢失是什么原因啊?
- Oracle 如何动态生态生成查询条件
- 有没有通过命令读取EXCEL表里的内容呢,像SQL SERVER 的OpenDataSource
- 我建了快照,为什么查询速度反而慢了呢?
- orcale起步问题,有分
- 如何写一条语句修改一个表结构的一个字段的名称
- 急急!oracle8.04小问题??????
- ORA-01401: 插入的值对于列过大
- BS 架构的系统,数据库是Oracle 11 现在遇到问题,扫入条码系统很慢
- 有没有一种函数可以根据输入值判断后添加一段在where后面的代码?
SELECT B.分类,记录总数,
NVL("1月",0) "1月", NVL("2月",0) "2月", NVL("3月",0) "3月", NVL("4月",0) "4月",
NVL("5月",0) "5月", NVL("6月",0) "6月", NVL("7月",0) "7月", NVL("8月",0) "8月",
NVL("9月",0) "9月", NVL("10月",0) "10月",NVL("11月",0) "11月",NVL("12月",0) "12月"
FROM (
SELECT 'A' AS 分类 FROM DUAL UNION ALL SELECT 'B' AS 分类 FROM DUAL UNION ALL
SELECT 'C' AS 分类 FROM DUAL UNION ALL SELECT 'D' AS 分类 FROM DUAL UNION ALL
SELECT 'E' AS 分类 FROM DUAL
)A,(
SELECT 分类,count(1) 记录总数,
sum(decode(to_char(创建时间,'mm'),'01',1)) "1月",sum(decode(to_char(创建时间,'mm'),'02',1)) "2月",
sum(decode(to_char(创建时间,'mm'),'03',1)) "3月",sum(decode(to_char(创建时间,'mm'),'04',1)) "4月",
sum(decode(to_char(创建时间,'mm'),'05',1)) "5月",sum(decode(to_char(创建时间,'mm'),'06',1)) "6月",
sum(decode(to_char(创建时间,'mm'),'07',1)) "7月",sum(decode(to_char(创建时间,'mm'),'08',1)) "8月",
sum(decode(to_char(创建时间,'mm'),'09',1)) "9月",sum(decode(to_char(创建时间,'mm'),'10',1)) "10月",
sum(decode(to_char(创建时间,'mm'),'11',1)) "11月",sum(decode(to_char(创建时间,'mm'),'12',1)) "12月"
FROM T
where to_char(创建时间,'yyyy')='2014'
group by 分类
)B
WHERE A.分类(+)=B.分类
order by A.分类