-- 查询2011年10月31日的最后时间数据 select t.dtime,t.fa from ha01 t where t.dtime = (select max(dtime) from ha01 where to_char(dtime,'yyyymmdd') = '20111031');
with a as (select t.unitime, row_number() over (partition by to_char(t.unitime, 'yyyymmdd') order by t.unitime desc)as rownumber from ha01 t) select a.unitime from a where a.rownumber = 1;
select t.dtime,t.fa from ha01 t where t.dtime in (select max(dtime) from ha01 where dtime between to_date(dtime, 'yyyy-mm-dd') and to_date(dtime, 'yyyy-mm-dd'))
-- 建立数据库表t1 CREATE TABLE t1 ( Id VARCHAR(10), MyTime DATE );-- 插入数据 INSERT INTO t1 VALUES('1', to_date('2011-11月-07 08:00:00', 'YYYY-Month-dd HH:MI:SS')); INSERT INTO t1 VALUES('2', to_date('2011-11月-07 09:00:00', 'YYYY-Month-dd HH:MI:SS')); INSERT INTO t1 VALUES('3', to_date('2011-11月-07 10:00:00', 'YYYY-Month-dd HH:MI:SS')); INSERT INTO t1 VALUES('4', to_date('2011-11月-08 08:00:00', 'YYYY-Month-dd HH:MI:SS')); INSERT INTO t1 VALUES('5', to_date('2011-11月-08 09:00:00', 'YYYY-Month-dd HH:MI:SS')); INSERT INTO t1 VALUES('6', to_date('2011-11月-09 08:00:00', 'YYYY-Month-dd HH:MI:SS')); INSERT INTO t1 VALUES('7', to_date('2011-11月-09 09:00:00', 'YYYY-Month-dd HH:MI:SS')); INSERT INTO t1 VALUES('8', to_date('2011-11月-09 10:00:00', 'YYYY-Month-dd HH:MI:SS'));-- 查询11年11月8日1:00到11年11月10日1:00之间的最大的。 select max(mytime) from t1 where mytime between to_date('2011-11月-08 01:00:00', 'YYYY-Month-dd HH:MI:SS') and to_date('2011-11月-10 01:00:00', 'YYYY-Month-dd HH:MI:SS')结果为:2011-11-09 10:00:00
select * from ( select t.dtime,t.fa from ha01 t where t.dtime='某个时间值' order by dtime desc ) tb where rownum=1;select * from ( select t.dtime,t.fa from ha01 t where t.dtime between 'startime' and 'endtime' order by dtime desc ) tb where rownum=1;
-- 查询2011年10月31日的最后时间数据
select t.dtime,t.fa
from ha01 t
where t.dtime = (select max(dtime) from ha01 where to_char(dtime,'yyyymmdd') = '20111031');
(select t.unitime, row_number() over (partition by to_char(t.unitime, 'yyyymmdd') order by t.unitime desc)as rownumber
from ha01 t)
select a.unitime from a
where a.rownumber = 1;
where t.dtime in
(select max(dtime)
from ha01
where dtime between to_date(dtime, 'yyyy-mm-dd') and
to_date(dtime, 'yyyy-mm-dd'))
-- 建立数据库表t1
CREATE TABLE t1
(
Id VARCHAR(10),
MyTime DATE
);-- 插入数据
INSERT INTO t1 VALUES('1', to_date('2011-11月-07 08:00:00', 'YYYY-Month-dd HH:MI:SS'));
INSERT INTO t1 VALUES('2', to_date('2011-11月-07 09:00:00', 'YYYY-Month-dd HH:MI:SS'));
INSERT INTO t1 VALUES('3', to_date('2011-11月-07 10:00:00', 'YYYY-Month-dd HH:MI:SS'));
INSERT INTO t1 VALUES('4', to_date('2011-11月-08 08:00:00', 'YYYY-Month-dd HH:MI:SS'));
INSERT INTO t1 VALUES('5', to_date('2011-11月-08 09:00:00', 'YYYY-Month-dd HH:MI:SS'));
INSERT INTO t1 VALUES('6', to_date('2011-11月-09 08:00:00', 'YYYY-Month-dd HH:MI:SS'));
INSERT INTO t1 VALUES('7', to_date('2011-11月-09 09:00:00', 'YYYY-Month-dd HH:MI:SS'));
INSERT INTO t1 VALUES('8', to_date('2011-11月-09 10:00:00', 'YYYY-Month-dd HH:MI:SS'));-- 查询11年11月8日1:00到11年11月10日1:00之间的最大的。
select max(mytime) from t1
where mytime between to_date('2011-11月-08 01:00:00', 'YYYY-Month-dd HH:MI:SS')
and to_date('2011-11月-10 01:00:00', 'YYYY-Month-dd HH:MI:SS')结果为:2011-11-09 10:00:00
select * from
(
select t.dtime,t.fa from ha01 t where t.dtime='某个时间值' order by dtime desc
) tb
where rownum=1;select * from
(
select t.dtime,t.fa from ha01 t where t.dtime between 'startime' and 'endtime' order by dtime desc
) tb
where rownum=1;