select * from(select t.*, row_number()over(order by t.时间字段 desc) row_ from dual t where to_char(时间子段,'day')=to_char(sysdate,'day')) where row_ <= 2;
select t1.* from dual t1, (select data,max(time) time from dual group by data)t2 where t1.data=t2.data and (t1.time=t2.time or t1.time=(t2.time-7))
这样写会遇到一个问题,如果上周同时间没有数据,那么,上上周的数据就会被select出来了
dual是Oracle系统的一个虚表,可以用做自定义表的名字吗?
楼主没有说明同时间的概念是同一天还是同一秒.以下代码中不考虑dual是否可以建表的问题。-- 同一秒的算法 SELECT A.* FROM DUAL A,(SELECT MAX(TIME) TM FROM DUAL ) B WHERE A.TIME = B.TIME OR A.TIME = B.TIME - 7 ;-- 同一天的算法 SELECT A.* FROM DUAL A,(SELECT MAX(TIME) TM FROM DUAL ) B WHERE A.TIME = B.TIME OR trunc(A.TIME) = trunc(B.TIME - 7) ;
WITH T AS (SELECT SYSDATE 时间字段 FROM DUAL UNION ALL --SELECT SYSDATE-7 时间字段 from dual UNION ALL SELECT SYSDATE - 14 时间字段 FROM DUAL) SELECT * FROM (SELECT NVL(时间字段, T2.A), ROW_NUMBER() OVER(ORDER BY T.时间字段) ROW_ FROM T RIGHT JOIN (SELECT TRUNC(SYSDATE - (LEVEL - 1)) A, LEVEL - 1 L FROM DUAL CONNECT BY LEVEL <= 8) T2 ON TO_CHAR(时间字段, 'day') = TO_CHAR(SYSDATE, 'day') AND TRUNC(时间字段) = T2.A WHERE MOD(L + 7, 7) = 0) WHERE ROW_ <= 2
select *
from(select t.*,
row_number()over(order by t.时间字段 desc) row_
from dual t
where to_char(时间子段,'day')=to_char(sysdate,'day'))
where row_ <= 2;
t1.data=t2.data and (t1.time=t2.time or t1.time=(t2.time-7))
楼主没有说明同时间的概念是同一天还是同一秒.以下代码中不考虑dual是否可以建表的问题。-- 同一秒的算法
SELECT A.* FROM DUAL A,(SELECT MAX(TIME) TM FROM DUAL
) B WHERE A.TIME = B.TIME OR A.TIME = B.TIME - 7 ;-- 同一天的算法
SELECT A.* FROM DUAL A,(SELECT MAX(TIME) TM FROM DUAL
) B WHERE A.TIME = B.TIME OR trunc(A.TIME) = trunc(B.TIME - 7) ;
(SELECT SYSDATE 时间字段
FROM DUAL
UNION ALL
--SELECT SYSDATE-7 时间字段 from dual UNION ALL
SELECT SYSDATE - 14 时间字段 FROM DUAL)
SELECT *
FROM (SELECT NVL(时间字段, T2.A),
ROW_NUMBER() OVER(ORDER BY T.时间字段) ROW_
FROM T
RIGHT JOIN (SELECT TRUNC(SYSDATE - (LEVEL - 1)) A, LEVEL - 1 L
FROM DUAL
CONNECT BY LEVEL <= 8) T2
ON TO_CHAR(时间字段, 'day') = TO_CHAR(SYSDATE, 'day')
AND TRUNC(时间字段) = T2.A
WHERE MOD(L + 7, 7) = 0)
WHERE ROW_ <= 2