如何分类标记？

Question

表1
name start end1 strand
CDS1 25 30 12001.m07002
CDS2 35 80 12001.m07002
CDS3 100 180 12001.m07002
mRNA 1 200 12001.m07002
表2
mirna           start   end      gene_model
osa-miR1 44 65 12001.m07002
osa-miR2 120 165 12001.m07002
...
想要的结果是
表3
mirna           start   end      gene_model    location
osa-miR1 44 65 12001.m07002     CDS1
osa-miR2 120 165 12001.m07002     CDS3
osa-miR3 185 195 12001.m07002     3
osa-miR4 4 15 12001.m07002     5
通过判断表中的miRNA在表1中的位置分别标记上，如果不在表1  start和end 范围内的标记为5 和 3 ，落在CDS1之前的标记为5，落在最后一个cds3之后的标记为3

Answer 1

你问过相似的，用case when....

Answer 2

那次是没有标记具体的，现在要标记为具体的如是CDS1而不仅仅是CDS

Answer 3

如果没弄错，表1中的strand 应该是gene_model吧？

Answer 4

想要的结果是
表3
mirna          start  end      gene_model    location
osa-miR1 44 65 12001.m07002    CDS1 应该是CDS2吧？

Answer 5

纠正版表1 
name start end1 gene_model 
CDS1 25 30     12001.m07002 
CDS2 35 80     12001.m07002 
CDS3 100 180   12001.m07002 
mRNA 1 200     12001.m07002 
表2 
mirna          start  end      gene_model 
osa-miR1        44    65       12001.m07002 
osa-miR2        120   165      12001.m07002 
... 
想要的结果是 
表3 
mirna          start  end      gene_model    location 
osa-miR1        44     65      12001.m07002    CDS2 
osa-miR2        120    165     12001.m07002    CDS3 
osa-miR3        185    195     12001.m07002    3 
osa-miR4         4      15     12001.m07002    5 
通过判断表中的miRNA在表1中的位置分别标记上，如果不在表1  start和end 范围内的标记为5 和 3 ，落在CDS1之前的标记为5，落在最后一个cds3之后的标记为3 

Answer 6

就是将表2中的每个位置定位在表1中。举个例子：就是将表2中的每个值与同组内（gene-model分组)的各项进行比较，举个例子：表2中的osa-miR1 属于 12001.m07002 这一组内，而表1中12001.m07002 组包含四项，以mRNA为首的下面包含3个CDS区段，然后将osa-miR1的start和end与各个CDS区段比较，最后发现 osa-miR1.start>CDS2.start 且  osa-miR1.end<CDS2.end ,则location列就标记为CDS2 。不知道这样解释能否明白？

Answer 7

/*
纠正版表1
name start end1 gene_model
CDS1 25 30    12001.m07002
CDS2 35 80    12001.m07002
CDS3 100 180  12001.m07002
mRNA 1 200    12001.m07002
表2
mirna          start  end      gene_model
osa-miR1        44    65      12001.m07002
osa-miR2        120  165      12001.m07002
...
想要的结果是
表3
mirna          start  end      gene_model    location
osa-miR1        44    65      12001.m07002    CDS2
osa-miR2        120    165    12001.m07002    CDS3
osa-miR3        185    195    12001.m07002    3
osa-miR4        4      15    12001.m07002    5
通过判断表中的miRNA在表1中的位置分别标记上，如果不在表1  start和end 范围内的标记为5 和 3 ，落在CDS1之前的标记为5，落在最后一个cds3之后的标记为3
*/
select distinct *
from (
select distinct b.*,
(case when b.start >a.start and b.start < a.end1 then a.[name] 
when b.start < c.start_a then '5'  when b.start > c.start_b  then '3' end
) location
from tableB b
join tableA a on b.gene_model=a.gene_model and a.[name] <> 'mRNA'
join (select distinct start_a=(select start from tableA where [name]='CDS1'),
start_b=(select end1 from tableA where [name]='CDS3'),gene_model
from tableA) c on b.gene_model=c.gene_model
)  d where d.location is not NUll
/*
miRNA start end gene_model location
osa-miR1 44 65 12001.m07002 CDS2
osa-miR2 120 165 12001.m07002 CDS3
osa-miR3 185 195 12001.m07002 3
osa-miR4 4 15 12001.m07002 5
*/