douzhe9927
douzhe9927
2018-04-27 09:33
浏览 62
已采纳

mysql选择具有相同ID的行,并按另一列的顺序选择

+-------+-----------+------------+
| id    | sequence  | text       |
+-------+-----------+------------+
| 12345 | 01        | Hello, my  | 
| 12345 | 02        | name is CJ | 
| 54321 | 01        | Good morn  | 
| 54321 | 02        | ing! Sup!  | 
+-------+-----------+------------+

I am totally lost. There are 2 messages in these rows 1) "Hello, my name is CJ" and 2) "Good morning! Sup!"

How could I display both of these messages by selecting them by IDs and with correct order(sequence)?

So the results would be

"Hello, my name is CJ"

"Good morning! Sup!"

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dongshan1811
    dongshan1811 2018-04-27 09:45
    已采纳
    SELECT id,GROUP_CONCAT(text ORDER BY sequence  SEPARATOR ' ') AS messages
    FROM table1
    GROUP BY id;
    

    Output

    id      messages
    12345   Hello, my name is CJ
    54321   Good morning! Sup!
    

    Demo

    http://sqlfiddle.com/#!9/f38d19/5

    GROUP_CONCAT() function

    MySQL GROUP_CONCAT() function returns a string with concatenated non-NULL value from a group.

    Syntax :

    GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])
    
    点赞 评论
  • doubianyan9749
    doubianyan9749 2018-04-27 09:39

    TRY GROUP_CONCAT

    SELECT id,GROUP_CONCAT(text SEPARATOR ' ')
    FROM tableA
    GROUP BY id;
    
    点赞 评论
  • duanci5913
    duanci5913 2018-04-27 09:43
    select GROUP_CONCAT(text SEPARATOR ' ')
    from table_name
    group by id
    
    点赞 评论

相关推荐