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.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值