A 表
Name Count Date
AA 2 2009/11/10
AA 5 2009/7/10
AA 3 2009/6/11
AA 9 2009/11/18
AA 4 2009/2/10
AA 6 2009/9/14
AA 3 2009/3/10
AA 6 2009/4/10
AA 8 2009/11/1
AA 5 2009/11/19
AA 4 2009/2/10
AA 6 2008/11/15
查詢條件 為: 2009年
需要得到的結果為: A表
Date Name Count
1月 AA 0
2月 AA 8
3月 AA 10
4月 AA 6
5月 AA 0
6月 AA 3
7月 AA 5
8月 AA 0
9月 AA 6
10月 AA 0
11月 AA 24
12月 AA 0
謝謝!
Name Count Date
AA 2 2009/11/10
AA 5 2009/7/10
AA 3 2009/6/11
AA 9 2009/11/18
AA 4 2009/2/10
AA 6 2009/9/14
AA 3 2009/3/10
AA 6 2009/4/10
AA 8 2009/11/1
AA 5 2009/11/19
AA 4 2009/2/10
AA 6 2008/11/15
查詢條件 為: 2009年
需要得到的結果為: A表
Date Name Count
1月 AA 0
2月 AA 8
3月 AA 10
4月 AA 6
5月 AA 0
6月 AA 3
7月 AA 5
8月 AA 0
9月 AA 6
10月 AA 0
11月 AA 24
12月 AA 0
謝謝!
解决方案 »
- oracle 9i ORA-00020
- 请教when中空字符串写法
- web开发时, 后台数据库oracle并发访问问题,
- oracle10g如何热备,有的说用dataguad?,有具体说明如何做的吗???
- [请教]初学者,望高手来解决我的疑惑
- ORACLE﹐路過的大俠來看看
- 请问这是 Oracle 10g 的bug 吗?
- 求助!!!!!!!!!!!!!!!!!!!!!
- 我刚开始学Oracle,在安装和练习的过程中,发现Oracle有好几个默认的帐户,哪位给讲讲这几个帐户的区别?
- 请问这样的SQL语句错在哪里?(在线等待)
- 求助各位大虾,本科毕业设计做Oracle数据库方面的可以做什么?
- 六万条记录如何最快导入数据库
date 字段是什么类型的?
1. Date 是什么类型的?
2. 结果集中的count是怎么算出来的?
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
union all select 'AA',5,date'2009-7-10' from dual
union all select 'AA',3,date'2009-6-11' from dual
union all select 'AA',9,date'2009-11-18' from dual
union all select 'AA',4,date'2009-2-10' from dual
union all select 'AA',6,date'2009-9-10' from dual
union all select 'AA',3,date'2009-3-10' from dual
union all select 'AA',6,date'2009-4-10' from dual
union all select 'AA',8,date'2009-11-10' from dual
union all select 'AA',5,date'2009-11-10' from dual
union all select 'AA',4,date'2009-2-10' from dual
union all select 'AA',6,date'2009-11-10' from dual)
select to_char(tmp.dt,'mm"月"')dt,tmp.name,sum(nvl(a.count,0))count
from(select * from
(select add_months(date'2009-1-1',rownum-1) dt from all_objects
where rownum<=12),
(select distinct name from a))tmp,
a
where tmp.name=a.name(+)
and tmp.dt=trunc(a.dt(+),'mm')
group by tmp.dt,tmp.name
order by 2,1
Count 是: 如上表 11月 有4筆數據 2,9,8,5 的總和
2 select 'AA' cname,2 tcount,to_date('2009/11/10','yyyy-mm-dd') tDate from dual
3 union ALL
4 select 'AA',5,to_date('2009/7/10','yyyy-mm-dd') FROM DUAL
5 union ALL
6 select 'AA',3,to_date('2009/6/11','yyyy-mm-dd') FROM DUAL
7 union ALL
8 select 'AA',9,to_date('2009/11/18','yyyy-mm-dd') FROM DUAL
9 union ALL
10 select 'AA',4,to_date('2009/2/10','yyyy-mm-dd') FROM DUAL
11 union ALL
12 select 'AA',6,to_date('2009/9/14','yyyy-mm-dd') FROM DUAL
13 union ALL
14 select 'AA',3,to_date('2009/3/10','yyyy-mm-dd') FROM DUAL
15 union ALL
16 select 'AA',6,to_date('2009/4/10','yyyy-mm-dd') FROM DUAL
17 union ALL
18 select 'AA',8,to_date('2009/11/1','yyyy-mm-dd') FROM DUAL
19 union ALL
20 select 'AA',5,to_date('2009/11/19','yyyy-mm-dd') FROM DUAL
21 union ALL
22 select 'AA',4,to_date('2009/2/10','yyyy-mm-dd') FROM DUAL
23 union ALL
24 select 'AA',6,to_date('2008/11/15','yyyy-mm-dd') FROM DUAL
25 )
26 SELECT A.MON||'月',DECODE(B.CNAME,NULL,'AA',B.CNAME) CNAME,SUM(DECODE(B.TCOUNT,NULL,0,B.TCOUNT)) COUNT FROM (
27 SELECT TO_CHAR(level,'00') MON from dual connect by level<=12
28 ) A LEFT OUTER JOIN (
29 select * from ttT WHERE TO_CHAR(TDATE,'YYYY')='2009'
30 ) B ON TRIM(A.MON)=TO_CHAR(B.TDATE,'MM')
31 GROUP BY A.MON,B.CNAME
32 ORDER BY A.MON ;A.MON||'月' CNAME COUNT
----------- ----- ----------
01月 AA 0
02月 AA 8
03月 AA 3
04月 AA 6
05月 AA 0
06月 AA 3
07月 AA 5
08月 AA 0
09月 AA 6
10月 AA 0
11月 AA 24
12月 AA 012 rows selected
就这条语句就可以搞定
有那么复杂吗
create table herry_tmp (Name varchar2(10), A_Count number, A_Date date)
--Insert data
insert into herry_tmp values ('AA', 2, to_date('2009/11/10', 'YYYY/MM/DD'));
insert into herry_tmp values ('AA', 5, to_date('2009/07/10', 'YYYY/MM/DD'));
insert into herry_tmp values ('AA', 3, to_date('2009/06/11', 'YYYY/MM/DD'));
insert into herry_tmp values ('AA', 9, to_date('2009/11/10', 'YYYY/MM/DD'));
insert into herry_tmp values ('AA', 4, to_date('2009/02/10', 'YYYY/MM/DD'));
insert into herry_tmp values ('AA', 6, to_date('2009/04/10', 'YYYY/MM/DD'));
insert into herry_tmp values ('AA', 8, to_date('2009/11/1', 'YYYY/MM/DD'));
insert into herry_tmp values ('AA', 3, to_date('2009/03/10', 'YYYY/MM/DD'));
insert into herry_tmp values ('AA', 5, to_date('2009/11/19', 'YYYY/MM/DD'));
insert into herry_tmp values ('AA', 4, to_date('2009/02/10', 'YYYY/MM/DD'));
insert into herry_tmp values ('AA', 6, to_date('2008/11/15', 'YYYY/MM/DD'));
insert into herry_tmp values ('AB', 6, to_date('2008/11/15', 'YYYY/MM/DD'));
--Query
SELECT b.MONTH "Date", nvl(a.cnt, 0) "Count", c.name
FROM (SELECT to_char(ii.a_date, 'MM') MONTH, sum(ii.a_count) cnt
FROM herry_tmp ii
where ii.name = 'AB'
GROUP BY to_char(ii.a_date, 'MM')
ORDER BY to_char(ii.a_date, 'MM')) a,
(select trim(to_char(rownum, '09')) month
from herry_tmp
where rownum <= 12) b,
(select distinct name from herry_tmp) c
WHERE a.MONTH(+) = b.month
and c.name = 'AB'
ORDER BY b.MONTH;
select to_char(Data,'mm'),Name,Count from A where to_char(Data,'yyyy')='2009' select to_char(sysdate,'mm') from dual where to_char(sysdate,'yyyy')='2009'
select to_char(sysdate,'mm') || '月' as Data from dual where to_char(sysdate,'yyyy')='2009'
字母你咋加?总有算法的吧?忽略?变ASCII值?还是怎么着?