mysql group by 字符串连接,如何使用GROUP BY来连接mysql中的字符串

normal query

id sid string

5 1 AAA

6 1 BBB

7 2 CCC

8 3 ZZZ

9 3 EEE

i want

sid string

1 1. AAA 2. BBB

2 1. CCC

3 1. ZZZ 2. EEE

Do you have any idea how to do?

解决方案

You can use the GROUP_CONCAT() function get the values into a single row and you can use user-defined variables to assign the number to each value in the sid group:

select sid,

group_concat(concat(rn, '. ', string) ORDER BY id SEPARATOR ' ') string

from

(

select id,

sid,

string,

@row:=case when @prev=sid then @row else 0 end +1 rn,

@prev:=sid

from yourtable

cross join (select @row:= 0, @prev:=null) r

order by id

) src

group by sid

| SID | STRING |

-----------------------

| 1 | 1. AAA 2. BBB |

| 2 | 1. CCC |

| 3 | 1. ZZZ 2. EEE |