select t.* from tb t where not exists(select 1 from tb where trunc(add_months(sysdate,-1),'mm')=trunc(dt_col,'mm') and dt_col>t.dt_col)
--取上个月的值,用函数add_months() select last_month from tab where add_months(sysdate,-1)=last_month--只取一条,用row_number()或是min,max的函数 select last_month from ( select last_month,row_number()over(order by last_month) rn from tab where add_months(sysdate,-1)=last_month ) where rn=1
select * from (select * from tb1 a where add_months(sysdate,-1)=a.date order by a.id) where rownum<2
select id,name,tel,createtime from ( select id,name,tel,createtime,row_number()over(partition by createtime) rn from tab1 where trunc(createtime,'mm')=trunc(add_months(sysdate,-1),'mm') ) where rn=1
实测成功:CREATE TABLE tab1 ( ID NUMBER(4), NAME VARCHAR2(100), tel VARCHAR2(20), createtime DATE ); DELETE tab1; INSERT INTO tab1 VALUES(1, 'John', '111111', to_date('2011-10-13 20:00:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO tab1 VALUES(2, 'Peter', '222222', to_date('2011-10-13 20:10:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO tab1 VALUES(3, 'Tom', '333333', to_date('2011-10-14 20:15:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO tab1 VALUES(4, 'Adward', '444444', to_date('2011-11-20 17:15:00', 'YYYY-MM-DD HH24:MI:SS'));SELECT * FROM tab1;SELECT * FROM tab1 WHERE createtime = (SELECT MIN(createtime) FROM tab1 WHERE to_char(createtime, 'YYYY-MM') = to_char(add_months(SYSDATE, -1), 'YYYY-MM')); 结果:
select t.* from tb t where not exists(select 1 from tb where trunc(add_months(sysdate,-1),'mm')=trunc(dt_col,'mm') and dt_col<t.dt_col)
借用这位仁兄的例子: SELECT * FROM tab1 WHERE trunc(createtime,'mm')=trunc(add_months(sysdate,-1),'mm') and createtime<= ( SELECT min(createtime) FROM tab1 WHERE trunc(createtime,'mm')=trunc(add_months(sysdate,-1),'mm') )
select t.* from tb t where not exists(select 1 from tb where trunc(add_months(sysdate,-1),'mm')=trunc(dt_col,'mm') and dt_col>t.dt_col)
--取上个月的值,用函数add_months()
select last_month from tab where add_months(sysdate,-1)=last_month--只取一条,用row_number()或是min,max的函数
select last_month
from
(
select last_month,row_number()over(order by last_month) rn from tab where add_months(sysdate,-1)=last_month
)
where rn=1
(select * from tb1 a where add_months(sysdate,-1)=a.date order by a.id)
where rownum<2
字段: id(NUMBER)、name(VARCHAR2)、tel(VARCHAR2),createtime(date)
from
(
select id,name,tel,createtime,row_number()over(partition by createtime) rn from tab1
where trunc(createtime,'mm')=trunc(add_months(sysdate,-1),'mm')
)
where rn=1
(
ID NUMBER(4),
NAME VARCHAR2(100),
tel VARCHAR2(20),
createtime DATE
);
DELETE tab1;
INSERT INTO tab1 VALUES(1, 'John', '111111', to_date('2011-10-13 20:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO tab1 VALUES(2, 'Peter', '222222', to_date('2011-10-13 20:10:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO tab1 VALUES(3, 'Tom', '333333', to_date('2011-10-14 20:15:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO tab1 VALUES(4, 'Adward', '444444', to_date('2011-11-20 17:15:00', 'YYYY-MM-DD HH24:MI:SS'));SELECT * FROM tab1;SELECT * FROM tab1
WHERE createtime =
(SELECT MIN(createtime)
FROM tab1
WHERE to_char(createtime, 'YYYY-MM') = to_char(add_months(SYSDATE, -1), 'YYYY-MM'));
结果:
FROM tab1
WHERE trunc(createtime,'mm')=trunc(add_months(sysdate,-1),'mm')
and createtime<=
(
SELECT min(createtime)
FROM tab1
WHERE trunc(createtime,'mm')=trunc(add_months(sysdate,-1),'mm')
)