重复的DNA序列
所有 DNA 都由一系列缩写为 A,C,G 和 T 的核苷酸组成,例如:“ACGAATTCCG”。在研究 DNA 时,识别 DNA 中的重复序列有时会对研究非常有帮助。
编写一个函数来查找 DNA 分子中所有出现超过一次的 10 个字母长的序列(子串)。
示例:
输入:s = "AAAAACCCCCAAAAACCCCCCAAAAAGGGTTT"
输出:["AAAAACCCCC", "CCCCCAAAAA"]
使用Oracle11g数据库
最终SQL及结果:
SQL> select sub from 2 (select count(*) as cnt,sub from 3 (select substr(a.sery,b.rn,10) as sub 4 from tb_string a, 5 (select rownum as rn from dual connect by level<=(select length(max(sery)) from tb_string )) b) c 6 group by sub) d 7 where d.cnt>1; SUB -------------------- AAAAACCCCC CCCCCAAAAA
思考过程:
create table tb_string( id number(4,0) primary key, sery nvarchar2(50) not null) insert into tb_string(id,sery) values('1','AAAAACCCCCAAAAACCCCCCAAAAAGGGTTT'); select rownum from dual connect by level<=(select length(max(sery)) from tb_string ); select * from tb_string, (select rownum from dual connect by level<=(select length(max(sery)) from tb_string )) a select substr(a.sery,b.rn,10) as sub from tb_string a, (select rownum as rn from dual connect by level<=(select length(max(sery)) from tb_string )) b select count(*) as cnt,sub from (select substr(a.sery,b.rn,10) as sub from tb_string a, (select rownum as rn from dual connect by level<=(select length(max(sery)) from tb_string )) b) c group by sub select sub from (select count(*) as cnt,sub from (select substr(a.sery,b.rn,10) as sub from tb_string a, (select rownum as rn from dual connect by level<=(select length(max(sery)) from tb_string )) b) c group by sub) d where d.cnt>1
--2020年5月11日 --