MYSQL:表查询问题

Question

我在SQL上的桌子上有个问题。我有两个表：table 1 (8000 lines)由一列组成，其中一列只包含唯一的基因id's (ex. HxC4233)，table 2 (20000 lines)由两列组成，其中包含类似于表1的基因id，但是有一些重复的(所以不是唯一的)具有相应基因长度的列。问题是，一些相同的基因有不同的异构体(ex. HxC4233_i1, HxC4233_i2)，具有不同的长度，但我已经削减了我的基因id在这两栏中，只显示(HxC4233)。

我想要创建一个查询，由表一中所有唯一的基因ids和表二中相应的基因长度组成。但我只希望拥有独特的基因长度，而不是所有的基因长度。

我已经尝试编写一个命令来获得我想要的输出。但我正在接收所有的基因标识作为输出。这是我的命令：

SELECT DISTINCT uniqgene_id, allgene_len
--> FROM table1 RIGHT JOIN table2 
--> ON allgene_id = uniqgene_id; 

是否有可能只输出我独特的基因id而只提取其相应的基因长度？

DISTINCT命令不考虑具有不同长度的相似基因。

我想要的输出是只有唯一的基因id与其相应的长度。

我的输出应该包含大约8000行。

Answer 1

SELECT DISTINCT allgene_id, MAX(allgene_len)
FROM table2 GROUP BY allgene_id;

为了这个目的，没有必要加入这些表。

Answer 2

可以使用以下查询为各自的gene_len选择最高的gene_id记录：

select uniqgene_id, allgene_len
from
(SELECT uniqgene_id, allgene_len, dense_rank() over (partition by t1.uniqgene_id order by allgene_len desc) as rnk
FROM table1 t1 left join t2
ON t1.allgene_id = t2.uniqgene_id)
where rnk = 1
group by 1,2;