mysql合并来自同一个表的2个查询

Question

这是查询 1:

SELECT distinct c.chem_gene, m.String_name, m.ScopeNote 
FROM mesher m INNER JOIN chem_base c 
ON (c.chem_name = m.String_name AND m.ScopeNote <> '' )
where match (c.chem_gene) against('ACTN3, MTHFR' in boolean mode)
group by c.chem_gene, c.chem_name;

像这样输出 3 列:

'ACTN3', 'Estradiol', 'The 17-beta-isomer of estradiol...'

这是查询 2(采用第 2 列“雌二醇”的输出):

SELECT String10 FROM mesher where String_name = "Estradiol" AND String10 <>'' LIMIT 1;

在单列中输出单行:

'Estrogens'

我如何修改查询 1，以便对于返回的每一行，针对第二列(即“雌二醇”)中的结果进行额外查询以生成此输出:

 'ACTN3', 'Estradiol', 'The 17-beta-isomer of estradiol...', 'Estrogens'

Answer 1

如果我没理解错的话，你可以使用相关子查询:

SELECT c.chem_gene, m.String_name, m.ScopeNote,
       (SELECT mm.String10
        FROM mesher mm
        WHERE mm.String_name = m.String_name AND mm.String10 <> ''
        LIMIT 1
       ) 
FROM mesher m INNER JOIN
     chem_base c 
     ON c.chem_name = m.String_name AND m.ScopeNote <> '' 
WHERE match(c.chem_gene) against( 'ACTN3, MTHFR' in boolean mode)
GROUP BY c.chem_gene, c.chem_name, m.ScopeNote ;

select distinct 不是必需的。