mysql - 如何编写MySQL查询以通过从中选择一些值来生成新表？

我有一个名为orthologygen的表：

ko, gene
ko:1, has:1
ko:2, has:2
ko:3, has:3
ko:4, pps:4
ko:4, pps:5
ko:3, rno:3
ko:3, rno:4
ko:2, rno:6
ko:4, rno:7

我想使用MySQL生成一个新表，如下所示：

has,rno
has:1, rno:3
has:2, rno:4
has:3, rno:6

我当前的查询版本是这样的：

select t1.hsa, t2.rno
from
(SELECT @n := @n + 1 as id, o1.gene as hsa FROM orthologygen o1, (select @n := 0) m where gene like "hsa%") t1
join
(SELECT @n := @n + 1 as id, o2.gene as rno FROM orthologygen o2, (select @n := 0) m where gene like "rno%") t2
on t1.id = t2.id

但是，它返回空，您知道如何解决此问题吗？谢谢

最佳答案

哦，我懂了。您需要不同的变量并对其进行初始化：

select h.hsa, r.rno
from (select (@nh := @nh + 1) as id, o1.gene as hsa
      from orthologygen o1 cross join
           (select @nh := 0) m
      where gene like 'hsa%'
     ) h join
     (select (@nr := @nr + 1) as id, o2.gene as rno
      from orthologygen o2 cross join
           (select @nr := 0) m
      where gene like 'rno%'
     ) r
     on r.id = h.id