表:tab_scan_come
字段 bill_code scan_date
0000001 2008-8-1 10:59:59 (间隔超出1小时了)
0000001 2008-8-1 15:00:47
0000001 2008-8-1 16:00:47 *
0000002 2008-8-5 12:00:47
0000002 2008-8-5 12:59:47 *
0000003 2008-8-6 18:00:47需求:日期范围在 2008-8-1 12:00:00 到 2008-8-6 12:00:10 bill_code重复数据 日期间隔在1小时,最新数据查询结果:
bill_code scan_date
0000001 2008-8-1 16:00:47
0000002 2008-8-5 12:59:47
在线急等!做功能出现问题了`本人SQL能力有限希望高手解决下`谢谢!
字段 bill_code scan_date
0000001 2008-8-1 10:59:59 (间隔超出1小时了)
0000001 2008-8-1 15:00:47
0000001 2008-8-1 16:00:47 *
0000002 2008-8-5 12:00:47
0000002 2008-8-5 12:59:47 *
0000003 2008-8-6 18:00:47需求:日期范围在 2008-8-1 12:00:00 到 2008-8-6 12:00:10 bill_code重复数据 日期间隔在1小时,最新数据查询结果:
bill_code scan_date
0000001 2008-8-1 16:00:47
0000002 2008-8-5 12:59:47
在线急等!做功能出现问题了`本人SQL能力有限希望高手解决下`谢谢!
0000001 2008-8-1 10:59:59
0000001 2008-8-1 12:00:47
0000001 2008-8-1 13:00:47 *
0000001 2008-8-1 16:00:47
这组按你的意思是带星号的吗?
结果 bill_code scan_date
0000001 2008-8-1 16:00:47
0000002 2008-8-5 12:59:47
select bill_code, max(scan_date) from test t where scan_date between and to_date('2008-08-06 12:00:10','yyyy-mm-dd hh24:mi:ss') group by bill_code
select bill_code, max(scan_date) from tab_scan_come where scan_date between to_date('2008-08-01 12:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-06 12:00:10','yyyy-mm-dd hh24:mi:ss') group by bill_code
这个可以找出有重复数据的 一小时以内的还没想出来 嘎嘎~~
可能是我表达有问题,让很多朋友无法理解。先说下对不起!所谓间隔1小时代表:
bill_code scan_date
0000001 2008-8-1 10:59:59 (间隔超出1小时了)
0000001 2008-8-1 15:00:47
0000001 2008-8-1 16:00:47 * 编号相同的情况下,日期最新的和非最新日期的一个对比,第3行数据2008-8-1 16:00:47是最新的那么和他间隔时间为1小时的就只有第二条了,第一条已经超过1小时 所以查询出来应该只有0000001 2008-8-1 16:00:47 满足条件如果我现在解释的还不够清楚明白的话请大家看看查询结果就可以了`谢谢!
0000001 2008-8-1 10:59:59 (间隔超出1小时了)
0000001 2008-8-1 15:00:47
0000001 2008-8-1 16:00:47 *
select bill_code, max(scan_date) from tab_scan_come where scan_date between to_date('2008-08-01 12:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-06 12:00:10','yyyy-mm-dd hh24:mi:ss') group by bill_cod
) t2
where t1.scan_date >t2.scan_date + 1/24
)
注:1/24表示一小时
select * from
(
select bill_code, max(scan_date) scan_date from tab_scan_come where scan_date between to_date('2008-08-01 12:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-06 12:00:10','yyyy-mm-dd hh24:mi:ss') group by bill_cod
) t2
where t1.scan_date >t2.scan_date + 1/24
)
'2008-08-01 12:00:10' and '2008-08-06 12:00:10' group by bill_code having(count(*)>1)
select bill_code, max(scan_date) from t where bill_code in
(select bill_code from test t where scan_date between
'2008-08-01 12:00:10' and '2008-08-06 12:00:10' group by bill_code having(count(*)>1))