在单行中选择用新行分隔的多行 [英] Select Multiple rows in single column separated by New Line

问题描述

我有一个表，值看起来像这样.

I have a table with values look like this.

    Id Name Fruit
    ------------
    1 Jon  Apple
    ------------
    2 Jon  Orange
    ------------
    3 Jon  Grape
    ------------
    4 Mike Apple
    ------------
    5 Mike Orange
    -------------

如何在mysql中将列区分为类似的内容?

How to distinct the column into something like this in mysql?

    Name Fruit
    ----------
    Jon  Apple
         Orange
         Grape
    -----------
    Mike Apple
         Orange
    -----------

推荐答案

这应该做

SELECT name, GROUP_CONCAT(fruit SEPARATOR '\n') FROM your_table GROUP BY name

db中的演示

更新以添加编号:

SELECT name ,
       GROUP_CONCAT(CONCAT (rn,')',fruit) SEPARATOR '\n')
FROM (
    SELECT * 
        ,ROW_NUMBER() OVER (PARTITION BY name) AS rn
    FROM your_table
    ) SQ
GROUP BY name

演示在db&f; fiddle

这篇关于在单行中选择用新行分隔的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！