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

Question

我有一个名为 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

但是，它返回空，你知道如何解决这个问题吗？谢谢

Answer 1

哦，我明白了。您需要不同的变量并初始化它们:

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