SELECT T1.*
FROM (SELECT TO_CHAR(TO_DATE('2011-05-11 00:00:01',
'yyyy-mm-dd hh24:mi:ss') + (ROWNUM - 1) / 24,
'yyyy-mm-dd hh24') HH
FROM DUAL
CONNECT BY TO_DATE('2011-05-11 00:00:01', 'yyyy-mm-dd hh24:mi:ss') +
(ROWNUM - 1) / 24 <=
TO_DATE('2011-05-20 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) T1
WHERE NOT EXISTS
(SELECT 1
FROM TEST T2
WHERE T1.HH = TO_CHAR(T2.TRXN_DT, 'yyyy-mm-dd hh24'));
解决方案 »
- 修改log_archive_dest_1参数
- 各位大哥,在oracle中做ETL操作, 超大数据量插入表格,一亿条左右,大约100多G的数据,怎么样能快速插入
- 根据sysdate查数据?
- oracle odbc 读取 number问题
- 使用触发器后目标表数据正常添加了,源表数据反而没添加成功
- 大家看看存储过程代码有什么问题!!
- 项目开始动作了,我该从何入手来设计数据库了,请大家交流交流呀!!!
- 紧急求救,有知道关于oracle的书籍下载的地方了
- oracle 32位如何从R1升级到R2,求补丁和具体步骤。谢谢!!
- sqldeveloper每次登陆小黑框弹的好烦
- 菜鸟请教个各位大神!!急!!
- 能解释一下这个三表连接么?
SELECT T1.*
FROM (SELECT TO_CHAR(TO_DATE('2011-05-11 00:00:01',
'yyyy-mm-dd hh24:mi:ss') + (ROWNUM - 1) / 24,
'yyyy-mm-dd hh24') HH
FROM DUAL
CONNECT BY TO_DATE('2011-05-11 00:00:01', 'yyyy-mm-dd hh24:mi:ss') +
(ROWNUM - 1) / 24 <=
TO_DATE('2011-05-20 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) T1
WHERE NOT EXISTS
(SELECT 1
FROM TEST T2
WHERE T1.HH = TO_CHAR(T2.TRXN_DT, 'yyyy-mm-dd hh24'));
大哥,你这个验证过吗?我这里测试有问题啊
我的表名是:t_Pol_Gather_Gh_z 时间的字段是:f_time SELECT T1.*
FROM (SELECT TO_CHAR(TO_DATE('2011-05-11 00:00:01',
'yyyy-mm-dd hh24:mi:ss') + (ROWNUM - 1) / 24,
'yyyy-mm-dd hh24') HH
FROM DUAL
CONNECT BY TO_DATE('2011-05-11 00:00:01', 'yyyy-mm-dd hh24:mi:ss') +
(ROWNUM - 1) / 24 <=
TO_DATE('2011-05-20 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) T1
WHERE NOT EXISTS
(SELECT 1
FROM t_Pol_Gather_Gh_z T2
WHERE T1.HH = TO_CHAR(T2.f_Time, 'yyyy-mm-dd hh24'));这样写对吗?
SELECT T1.*
FROM (SELECT TO_CHAR(TO_DATE('2011-05-11 00:00:01',
'yyyy-mm-dd hh24:mi:ss') + (ROWNUM - 1) / 24,
'yyyy-mm-dd hh24') HH
FROM DUAL
CONNECT BY TO_DATE('2011-05-11 00:00:01', 'yyyy-mm-dd hh24:mi:ss') +
(ROWNUM - 1) / 24 <=
TO_DATE('2011-05-20 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) T1
WHERE NOT EXISTS
(SELECT 1
FROM TEST T2
WHERE T1.HH = TO_CHAR(T2.TRXN_DT, 'yyyy-mm-dd hh24'));
大哥,你这个验证过吗?我这里测试有问题啊
我的表名是:t_Pol_Gather_Gh_z 时间的字段是:f_time SELECT T1.*
FROM (SELECT TO_CHAR(TO_DATE('2011-05-11 00:00:01',
'yyyy-mm-dd hh24:mi:ss') + (ROWNUM - 1) / 24,
'yyyy-mm-dd hh24') HH
FROM DUAL
CONNECT BY TO_DATE('2011-05-11 00:00:01', 'yyyy-mm-dd hh24:mi:ss') +
(ROWNUM - 1) / 24 <=
TO_DATE('2011-05-20 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) T1
WHERE NOT EXISTS
(SELECT 1
FROM t_Pol_Gather_Gh_z T2
WHERE T1.HH = TO_CHAR(T2.f_Time, 'yyyy-mm-dd hh24'));这样写对吗?
验证过,你报什么错?
SELECT T1.*
FROM (SELECT TO_CHAR(TO_DATE('2011-05-11 00:00:01',
'yyyy-mm-dd hh24:mi:ss') + (ROWNUM - 1) / 24,
'yyyy-mm-dd hh24') HH
FROM DUAL
CONNECT BY TO_DATE('2011-05-11 00:00:01', 'yyyy-mm-dd hh24:mi:ss') +
(ROWNUM - 1) / 24 <=
TO_DATE('2011-05-20 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) T1
WHERE NOT EXISTS
(SELECT 1
FROM TEST T2
WHERE T1.HH = TO_CHAR(T2.TRXN_DT, 'yyyy-mm-dd hh24'));
大哥,你这个验证过吗?我这里测试有问题啊
我的表名是:t_Pol_Gather_Gh_z 时间的字段是:f_time SELECT T1.*
FROM (SELECT TO_CHAR(TO_DATE('2011-05-11 00:00:01',
'yyyy-mm-dd hh24:mi:ss') + (ROWNUM - 1) / 24,
'yyyy-mm-dd hh24') HH
FROM DUAL
CONNECT BY TO_DATE('2011-05-11 00:00:01', 'yyyy-mm-dd hh24:mi:ss') +
(ROWNUM - 1) / 24 <=
TO_DATE('2011-05-20 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) T1
WHERE NOT EXISTS
(SELECT 1
FROM t_Pol_Gather_Gh_z T2
WHERE T1.HH = TO_CHAR(T2.f_Time, 'yyyy-mm-dd hh24'));这样写对吗?
验证过,你报什么错?
倒不是报错,我感觉数据查出来的不对啊
在后面加个order by TO_DATE(t1.hh,'YYYY-MM-DD HH24')