mysql concat编码,GROUP_CONCAT编号

is it possible to have numbering in GROUP_CONCAT

like

If, from GROUP_CONCAT(empnam SEPARATOR ', ')

I get a set,

< JohnM, DannyP, TiffnyK, KarlM >

I need to have

< 1.JohnM, 2.DannyP, 3.TiffnyK, 4.KarlM >

I tried following, but didnt get desired results.

SET @x:=0;

SELECT

GROUP_CONCAT(@x:=@x+1,' ', s.empnam SEPARATOR ', ') AS emps, @x:=0

< tables >

< filters >

is it possible at Query-Level, or I have to do it at Application Side ?

解决方案

Years later, we should abandon mutating variables inside a select statement, as since MySQL 8 we can use the standard way, with window functions:

with base as (

select dep,

empnam,

count(*) over (partition by dep order by empnam) num

from t)

select dep,

group_concat(concat(num, '.', empnam) separator ', ') emps

from base

group by dep

Original answer (2016)

You can do this on the application side, but in MySQL 5.7 it is possible. In the following query, I assume you group the names by something, for example their department (I called it dep). This in order to illustrate that the counter starts from 1 for every new group.

select dep,

group_concat(

concat(@i := if (@grp = dep, @i + 1, if(@grp := dep,1,1)), '.', empnam)

separator ', ') emps

from t,

(select @i := 0, @grp := '') init

group by dep;

Make sure to put your table name in the from clause, and to use the actual field you want to group by. If you have multiple fields to group by, the expression assigned to @i will need to change. You could for instance concatenate the values that define a group.

By using a separator of two characters you ensure to have a space between each name.