Q200510-02-02: 重复的DNA序列 SQL解法

重复的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('','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日 --

巴特西

Q200510-02-02: 重复的DNA序列 SQL解法

最新文章

热门文章