17,086
社区成员
发帖
与我相关
我的任务
分享
with t as (
select '2001-10-1' d from dual union all
select '2001-10-2' d from dual union all
select '2001-10-3' d from dual union all
select '2002-11-1' d from dual union all
select '2002-11-3' d from dual union all
select '2006-1-2' d from dual union all
select '2007-2-1' d from dual union all
select '2008-11-1' d from dual)
SELECT NY || '-' || RM
FROM (SELECT DISTINCT REGEXP_SUBSTR(D, '[[:alnum:]]+', 1, 1) || '-' ||
REGEXP_SUBSTR(D, '[[:alnum:]]+', 1, 2) NY
FROM T),
(SELECT ROWNUM RM FROM DUAL CONNECT BY ROWNUM <= 3)
WHERE NOT EXISTS (SELECT 1 FROM T WHERE T.D = NY || '-' || RM)
ORDER BY NY, RM
[/quote]
非常感谢你。我改下到当前日期的话这样写的话还能优化吗?
with tab as (
select '2001-10-1' d from dual union all
select '2001-10-2' d from dual union all
select '2001-10-3' d from dual union all
select '2002-11-1' d from dual union all
select '2002-11-3' d from dual union all
select '2006-1-2' d from dual union all
select '2007-2-1' d from dual union all
select '2008-11-1' d from dual)
select ss from
(SELECT SN + (level - 1) SS
FROM (select min(to_date(substr(d,0,instr(d,'-',1,2)-1),'yyyy-mm')) SN from tab) T
CONNECT BY SN + (level - 1) <= sysdate-1 ) t
where to_char(t.ss,'dd') <=3
and ss not in (select to_date(d,'yyyy-mm-dd') from tab)with t as (
select '2001-10-1' d from dual union all
select '2001-10-2' d from dual union all
select '2001-10-3' d from dual union all
select '2002-11-1' d from dual union all
select '2002-11-3' d from dual union all
select '2006-1-2' d from dual union all
select '2007-2-1' d from dual union all
select '2008-11-1' d from dual)
SELECT NY || '-' || RM
FROM (SELECT DISTINCT REGEXP_SUBSTR(D, '[[:alnum:]]+', 1, 1) || '-' ||
REGEXP_SUBSTR(D, '[[:alnum:]]+', 1, 2) NY
FROM T),
(SELECT ROWNUM RM FROM DUAL CONNECT BY ROWNUM <= 3)
WHERE NOT EXISTS (SELECT 1 FROM T WHERE T.D = NY || '-' || RM)
ORDER BY NY, RM
with t as (
select '2001-10-1' d from dual union all
select '2001-10-2' d from dual union all
select '2001-10-3' d from dual union all
select '2002-11-1' d from dual union all
select '2002-11-3' d from dual union all
select '2006-1-2' d from dual union all
select '2007-2-1' d from dual union all
select '2008-11-1' d from dual)
SELECT NY || '-' || RM
FROM (SELECT DISTINCT REGEXP_SUBSTR(D, '[[:alnum:]]+', 1, 1) || '-' ||
REGEXP_SUBSTR(D, '[[:alnum:]]+', 1, 2) NY
FROM T),
(SELECT ROWNUM RM FROM DUAL CONNECT BY ROWNUM <= 3)
WHERE NOT EXISTS (SELECT 1 FROM T WHERE T.D = NY || '-' || RM)
ORDER BY NY, RM
[/quote]
非常感谢你。我改下到当前日期的话这样写的话还能优化吗?
with tab as (
select '2001-10-1' d from dual union all
select '2001-10-2' d from dual union all
select '2001-10-3' d from dual union all
select '2002-11-1' d from dual union all
select '2002-11-3' d from dual union all
select '2006-1-2' d from dual union all
select '2007-2-1' d from dual union all
select '2008-11-1' d from dual)
select ss from
(SELECT SN + (level - 1) SS
FROM (select min(to_date(substr(d,0,instr(d,'-',1,2)-1),'yyyy-mm')) SN from tab) T
CONNECT BY SN + (level - 1) <= sysdate-1 ) t
where to_char(t.ss,'dd') <=3
and ss not in (select to_date(d,'yyyy-mm-dd') from tab)[/quote]
本身数据集也不高,怎么写都行。。实现了就行啊。。