在MySQL中串联具有相同ID的行的字段 [英] Concatenate fields of rows with the same ID in MySQL

问题描述

我有以下查询:

SELECT mutations.id, genes.loc FROM mutations, genes where mutations.id=genes.id;

并输出:

| SL2.50ch02_51014904 | intergenic    |
| SL2.50ch02_51014907 | upstream      |
| SL2.50ch02_51014907 | downstream    |
| SL2.50ch02_51014907 | intergenic    |
| SL2.50ch02_51014911 | upstream      |
| SL2.50ch02_51014911 | downstream    |

我想要的输出是这样

| SL2.50ch02_51014904 | intergenic    |
| SL2.50ch02_51014907 | upstream,downstream,intergenic      |
| SL2.50ch02_51014911 | upstream,downstream      |

我认为GROUP_CONCAT对此很有用.但是，这样做:

I thought GROUP_CONCAT was useful for this. However, doing this:

SELECT mutations.id, GROUP_CONCAT(distinct(genes.loc)) FROM mutations, genes WHERE mutations.id=genes.id;

我有一个这样的独特行:

I have a unique row like this:

SL2.50ch02_51014904 | downstream,intergenic,upstream

我该如何解决?

推荐答案

您需要添加group by:

SELECT m.id, GROUP_CONCAT(distinct(g.loc)) 
FROM mutations m JOIN
    genes g
    ON m.id = g.id
GROUP BY m.id;

一路走来，您应该学到其他一些东西:

Along the way, you should learn a couple other things:

• 使用显式的join语法.一个简单的规则:切勿在from子句中使用逗号.
• 使用表别名(m和g).它们使查询更易于编写和阅读.

• Use explicit join syntax. A simple rule: never use commas in the from clause.
• Use table aliases (the m and g). They make the query easier to write and to read.

这篇关于在MySQL中串联具有相同ID的行的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！