表1结构如下
id content mdate(varchar2类型)
1 adadfs 2015-01-01
2 dsff 2015-01-03
3 dsgere 2015-01-04
4 kkll 2015-01-31
参数是2015-01 ，想要的结果是存在记录是1，不存在是0.
结果是1,0,1,1,0,0,0,....1
------解决思路----------------------
SELECT X.MDATE,
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'替换成相应的月份参数
------解决思路----------------------
with a as
( 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