mysql select语句，其中包含groupconcat

mrfwxfqh 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(352)

我喜欢这样的mysql语句

SELECT distinct mk.gene as gene, qs.rs as snp, CONCAT (qs.A1, qs.A2) as genotype
FROM dallas.QS_base qs  cross join   
dallas.staging mk 
     ON qs.rs = mk.rs 
WHERE qs.sampleID = 'mydna' 
order by gene ASC;

返回这种类型的输出

'ACE'    'RS4343',    'AA'
'ACTN3'  'RS1815739'  'TC'

从这种类型的表（dallas.staging）

'heart health', 'ACE', 'RS4343'
'skin health', 'ACE', 'RS4343'
'sports performance', 'ACE', 'RS4343'
'sports performance', 'ACTN3', 'RS1815739'
'longevity', 'ACTN3', 'RS1815739'

还有这个（达拉斯QSU基地）

'mydna','RS4343','A','A'
'mydna','RS1815739','T','C'

我应该如何更改上面的mysql语句来获得这个输出？我想我需要使用组\u concat命令。

'ACE'    'RS4343',    'AA' '(heart health, sports performance, skin health)'
'ACTN3'  'RS1815739'  'TC' '(sports performance, longevity)'

mysql group-concat

来源：https://stackoverflow.com/questions/49817338/mysql-select-statement-that-includes-group-concat

1条答案

按热度按时间

z18hc3ub1#

此查询应该为您完成这项工作（请注意，mk.gene上的distinct是不必要的，因为您正在按它进行分组）：

SELECT mk.gene as gene, qs.rs as snp, CONCAT (qs.A1, qs.A2) as genotype, GROUP_CONCAT(mk.condition) AS conditions
FROM QS_base qs  cross join   
staging mk 
     ON qs.rs = mk.rs 
WHERE qs.sampleID = 'mydna'
group by gene
order by gene ASC;

输出：

gene    snp         genotype    conditions
ACE     RS4343      AA          heart health,sports performance,skin health
ACTN3   RS1815739   TC          sports performance,longevity

赞(0）回复(0）举报 2021-06-21

我来回答

mysql select语句，其中包含groupconcat

1条答案

相关问题

热门标签

最新问答