求sql,根据记录获取某月数据是否存在

zzxiaoma 2015-03-04 05:22:10
表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
...全文
209 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
zzxiaoma 2015-03-05
  • 打赏
  • 举报
回复
1楼的输出格式不太对,但是年月比较灵活,2楼是用到31,总是数据31位,也有好处,都不错
  • 打赏
  • 举报
回复
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 ( 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天有多条记录的情况了
  • 打赏
  • 举报
回复
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
小灰狼W 2015-03-04
  • 打赏
  • 举报
回复
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'替换成相应的月份参数

17,382

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧