GROUP_CONCAT编号 [英] GROUP_CONCAT numbering

问题描述

是否可以在GROUP_CONCAT中进行编号

is it possible to have numbering in GROUP_CONCAT

喜欢

如果是从GROUP_CONCAT(empnam SEPARATOR'，')

If, from GROUP_CONCAT(empnam SEPARATOR ', ')

我得到了一套

 < JohnM, DannyP, TiffnyK, KarlM >

我需要

 < 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 ?

推荐答案

几年后，我们应该放弃select语句中的变量变异，因为自从MySQL 8以来，我们可以使用带有窗口函数的标准方法: >

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

请参见 db-fiddle

您可以在应用程序端执行此操作，但是在MySQL 5.7中是可行的.在以下查询中，我假设您按名称对名称进行分组，例如名称所属的部门(我称其为 dep ).这是为了说明每个新组的计数器都从1开始.

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;

请参见 SQL提琴 或 db-fiddle .

确保将表名放在from子句中，并使用要分组的实际字段.如果您有多个字段要分组，则分配给 @i 的表达式将需要更改.例如，您可以串联定义一个组的值.

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.

这篇关于GROUP_CONCAT编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！