如何从一个表中查询出某个字段连续重复3次的记录。
如:
序号 字段
1 A
2 B
3 B
4 A
5 B
6 B
7 B
8 A
9 B
10 A
11 A取到下面数据
序号 字段
5 B
6 B
7 B有什么好方法没?
如:
序号 字段
1 A
2 B
3 B
4 A
5 B
6 B
7 B
8 A
9 B
10 A
11 A取到下面数据
序号 字段
5 B
6 B
7 B有什么好方法没?
解决方案 »
- 我本机已有ORACE 10G client PL/SQL 已联上服务器 现在我用什么命令导出数据库 和 表呢
- 全两个表合并的sql
- sql 分组计数
- pgAdmin 连接另一台的PostgreSQL报错
- 简单_ SQL语句求教--- 请进.......
- Oracle9i中的Merge语句问题!!!请高手赐教!!!
- 登陆广域网中的oracle数据库,怎样才能不让管理员查出我的IP地址?
- 如何配置Net8 Configuration Assistant能够连接远程(公网)数据库
- ORACLE的系统用户有什么分别吗?
- 在线等待:oracle中创建可更新视图?
- oracle如何设置用户其默认表架构?
- 一些小问题
from table1
where 序号 in (
select 序号
from table1 a
where exists (select 1 from table1 where 序号=a.序号-1 and 字段=a.字段)
and exists (select 1 from table1 where 序号=a.序号+1 and 字段=a.字段)
)
or 序号-1 in (
select 序号
from table1 a
where exists (select 1 from table1 where 序号=a.序号-1 and 字段=a.字段)
and exists (select 1 from table1 where 序号=a.序号+1 and 字段=a.字段)
)
or 序号+1 in (
select 序号
from table1 a
where exists (select 1 from table1 where 序号=a.序号-1 and 字段=a.字段)
and exists (select 1 from table1 where 序号=a.序号+1 and 字段=a.字段)
)
select 1 id,'A' name from dual union all
select 2 id,'B' name from dual union all
select 3 id,'B' name from dual union all
select 4 id,'A' name from dual union all
select 5 id,'B' name from dual union all
select 6 id,'B' name from dual union all
select 7 id,'B' name from dual union all
select 8 id,'A' name from dual union all
select 9 id,'B' name from dual union all
select 10 id,'A' name from dual union all
select 11 id,'A' name from dual
)select id,name from(
select id,name,count(1)over(partition by rownum-level)flag
from tt t
start with id=1 or not exists(
select 1 from tt where id=t.id-1 and name=t.name)
connect by prior name=name
and prior id=id-1)
where flag>=3;
2 union
3 select 2 id,'B' name from dual
4 union
5 select 3 id,'B' name from dual
6 union
7 select 4 id,'A' name from dual
8 union
9 select 5 id,'B' name from dual
10 union
11 select 6 id,'B' name from dual
12 union
13 select 7 id,'B' name from dual
14 union
15 select 8 id,'A' name from dual
16 union
17 select 9 id,'B' name from dual
18 union
19 select 10 id,'A' name from dual
20 union
21 select 11 id,'A' name from dual
22 union
23 select 12 id,'A' name from dual
24 )
25 select a.*
26 from a,
27 (select id - 1 id1, id id2, id + 1 id3
28 from (select lead(name) over(order by id, name) rn,
29 lag(name) over(order by id, name) rn1,
30 name,
31 id
32 from a)
33 where rn = rn1
34 and rn1 = name) b
35 where a.id = b.id1
36 or a.id = b.id2
37 or a.id = b.id3
38 order by a.id
39 /
ID NAME
---------- ----
5 B
6 B
7 B
10 A
11 A
12 A
6 rows selected
SQL>
select 字段 from t group by 字段 having count(字段)=3
---------- ---
1 A
2 B
3 B
4 A
5 B
6 B
7 B
8 A
9 B
10 A
11 A已选择11行。已用时间: 00: 00: 00.01
15:54:57 sys@PRACTICE> select a.* from tb a,
15:55:05 2 (select id from (select id,count(1) over(partition by col order by id range between 0 preceding and 2 following) num from tb)
15:55:05 3 where num = 3) b
15:55:05 4 where a.id = b.id or a.id = b.id + 1 or a.id = b.id + 2 order by a.id; ID COL
---------- ---
5 B
6 B
7 B已用时间: 00: 00: 00.00