解决方案 »
- 权限问题
- oracle存储过程执行传入的sql语句
- (100分)关于两种语言转换问题请教。
- 编译失效过程
- 准备开始弄OCI相关的东西,大家帮忙给点意见和建议,能分享一下以前的心得就更好了。
- oracle 中创建视图的问题
- 在哪里找到oracle的数据库的配置文件?创建数据库时,想修改系统提供的默认参数,直接读自己写好的配置文件的参数。
- 各位有把Oracle放到公网上,客户端通过ADSL访问的方式么??安全怎么保障??
- pl/SQL的sql编辑界面中,是否可以执行批量sql语句?
- 高分请问:如何将 Ms Sql Server 7.0 中的数据库移植到 Oracle 8i 上呢?
- 关于Oracle客户端的问题
- 如何获取单条耗时最长的语句???
CASE
WHEN EXISTS (SELECT 1 FROM TABLE1 T1 WHERE T1.MDATE = X.MDATE) THEN
1
ELSE
0
END FLAG
FROM (SELECT TO_CHAR(TO_DATE('2015-01' || '-01', 'YYYY-MM-DD') + ROWNUM - 1,
'YYYY-MM-DD') MDATE
FROM DUAL
CONNECT BY ROWNUM <=
ADD_MONTHS(TO_DATE('2015-01' || '-01', 'YYYY-MM-DD'), 1) -
TO_DATE('2015-01' || '-01', 'YYYY-MM-DD')) X
'2015-01'替换成相应的月份参数
( select 1 as id, 'adadfs' as content, '2015-01-01' as mdate from dual
union all select 2, 'dsff', '2015-01-03' from dual
union all select 3, 'dsgere', '2015-01-04' from dual
union all select 4, 'kkll', '2015-01-31' from dual ),
b as
( select to_char(to_date('2015-01-01','yyyy-mm-dd') + level - 1,'yyyy-mm-dd') as sdate from dual connect by level <= 31 ),
c as
( select b.sdate, decode(a.id,null,0,1) as res
from a, b
where a.mdate(+) = b.sdate
order by 1 )
select dbms_lob.substr(wmsys.wm_concat(res),100) as res from c
( select 1 as id, 'adadfs' as content, '2015-01-01' as mdate from dual
union all select 2, 'dsff', '2015-01-03' from dual
union all select 3, 'dsgere', '2015-01-04' from dual
union all select 4, 'kkll', '2015-01-31' from dual ),
b as
( select to_char(to_date('2015-01-01','yyyy-mm-dd') + level - 1,'yyyy-mm-dd') as sdate from dual connect by level <= 31 ),
c as
( select b.sdate, decode(a.id,null,0,1) as res
from ( select mdate,count(1) as id from a group by mdate ) a, b
where a.mdate(+) = b.sdate
order by 1 )
select dbms_lob.substr(wmsys.wm_concat(res),100) as res from c
上面的忘了原表可能存在1天有多条记录的情况了