请教一句SQL语句，谢谢各位~~~

显示全部楼层 · 2011-11-20 09:10:00

本帖最后由 nuno_hh 于 2012-1-9 16:51 编辑
表TAB内有三个字段A、B、C、D，A为主键，具体数据如下
A
B
C
D
--------------------------------------------------
001 GGG 2011/11/20 09:10:00 1
002 BBB 2011/11/20 18:12:00 1
003 EEE 2011/11/21 01:10:00 1
004 DDD 2011/11/21 05:17:00 1
005 EEE 2011/11/21 20:10:00 0
006 FFF 2011/11/22 07:19:00 0
007 GGG 2011/11/22 10:10:00 0
008 HHH 2011/11/23 23:13:00 0
查询结果要求：从D=0中找是否有符合D=1、时间前后相差在24小时内、B相同的数据
上述数据中A=005这条数据符合
实在写不出，烦请各位大大们赐教！

create table TAB(
A varchar2(3),
B varchar2(3),
C date,
D char(1)
);
insert into TAB values ('001','GGG',to_date('2011/11/20 09:10:00', 'yyyy/mm/dd hh24:mi:ss'),'1');
insert into TAB values ('002','BBB',to_date('2011/11/20 18:12:00', 'yyyy/mm/dd hh24:mi:ss'),'1');
insert into TAB values ('003','EEE',to_date('2011/11/21 01:10:00', 'yyyy/mm/dd hh24:mi:ss'),'1');
insert into TAB values ('004','DDD',to_date('2011/11/21 05:17:00', 'yyyy/mm/dd hh24:mi:ss'),'1');
insert into TAB values ('005','EEE',to_date('2011/11/21 20:10:00', 'yyyy/mm/dd hh24:mi:ss'),'0');
insert into TAB values ('006','FFF',to_date('2011/11/22 07:19:00', 'yyyy/mm/dd hh24:mi:ss'),'0');
insert into TAB values ('007','GGG',to_date('2011/11/22 10:10:00', 'yyyy/mm/dd hh24:mi:ss'),'0');
insert into TAB values ('008','GGG',to_date('2011/11/23 23:13:00', 'yyyy/mm/dd hh24:mi:ss'),'0');

千问 · 2011-11-20 09:10:00

LZ,你先测试一下，如果有问题，把情况说明白！

SQL> select a, b, to_char(c, 'YYYY-MM-DD HH24:MI:SS') c, d from tmp;
ABC
D
-------- -------- ------------------- ----
001GGG2011-11-20 09:10:00 1
002BBB2011-11-20 18:12:00 1
003EEE2011-11-21 01:10:00 1
004DDD2011-11-21 05:17:00 1
005EEE2011-11-21 20:10:00 0
006FFF2011-11-22 07:19:00 0
007GGG2011-11-22 10:10:00 0
008HHH2011-11-23 23:13:00 0
8 rows selected
SQL>
SQL> selecta,
2
b,
3
to_date(c, 'YYYY-MM-DD HH24:MI:SS') c,
4
d
5from
6(select a,
7
b,
8
c,
9
d,
10
count(*) over(partition by b) cnt1,
11
count(*) over(partition by b order by c range between interval '24' hour preceding and interval '24' hour following) cnt2
12from tmp)
13where cnt1 > 1
14and cnt2 > 1
15and d = '0';
ABC D
-------- -------- ----------- ----
005EEE11/11/00210
SQL>

千问 · 2011-11-20 09:10:00

select *
from (select b.*, abs(b.c - lag(b.c)

over(partition by b.b order by b.c asc)) difflagbc
from b) t
where t.d = 0
and difflagbc > 0
and difflagbc0 and abs(a.c-b.c)<1;

千问 · 2011-11-20 09:10:00

231816133 发表于 2012-1-11 10:26
说实话那完全不要用分析函数
select b.*,a.c,abs(a.c-b.c) from tab a,tab b where b.d=0
and a.b=b.b an ...
你那24小时呢？