mysql - 将匹配 'numbers'的相对记录分配给mysql group_concat函数中的每条记录

Question

我使用 group_concat 和外连接来获取一对多关系中的所有记录，并在一行中返回结果。

查询如下所示:

select 
    t.seq as seq, 
    t.subject as subject, 
    t.location as location, 
    t.created as created, 
    t.completed as completed,
    group_concat(n.message, '<br>') as messages  
from tickets t left join tickets_notes n on n.ticket_seq = t.seq
group by t.seq;

我需要获取并显示每条消息的计数。换句话说，我需要告诉组 concat 函数每个注释的编号:(例如 group_concat('Note #', @thisnotesnumber, ' ', n.message, '
')。

我不知道从哪里提取“@thisnotenumber”。数据库知道 n.message 数组中存在多少个元素，但我不知道如何访问该数字并为输出增加它。

任何帮助将不胜感激。

谢谢。

<小时/>

所以我们已经非常接近了，但是以下几点:

select t.seq, t.subject, t.location, t.created, t.completed,
        group_concat(
                concat(
                'Note ',
                @rn := case
                    when @seq = t.seq then @rn + 1
                    when @seq := t.seq then 1
                    else 1
                end,
                ': ', 
                n.message 
                ) order by @rn separator '<br>'
            ) as messages  
from tickets t left join tickets_notes n
on n.ticket_seq = t.seq
group by t.seq 

反转前两个。第一个音符排在第二，编号为 1，表中第二个音符先显示，编号为 2。所以数字是正确的，但前两个的顺序颠倒了。

Answer 1

如果您运行的是 MySQL 8.0，则可以在聚合之前使用 row_number() 分配排名:

select 
    seq, 
    subject, 
    location, 
    created, 
    completed,
    group_concat(concat(rn, ': ', message) order by rn separator '<br>' ) messages
from (
    select 
        t.*, 
        n.message, 
        row_number() over(partition by t.seq order by n.id) rn
    from tickets t 
    left join tickets_notes n on n.ticket_seq = t.seq
) t
group by
    subject, 
    location, 
    created, 
    completed
;

注释:

• 为了获得一致的输出，您需要一些列来对 group_concat() 聚合的记录进行排序；我假设表 ticket_notes

中的列 id

• 虽然 seq 可能是 tickets 的主键，但您应该在 group by 子句中枚举所有非聚合列。从 MySQL 5.7 开始，默认情况下这是强制的

• 默认情况下，列名保留在结果集中；像 t.seq as seq 这样的别名是多余的(当您想要更改结果集中的列名称时，请使用别名)

<小时/>

在早期版本中，我们可以尝试使用变量:

select 
    t.seq, 
    t.subject, 
    t.location, 
    t.created, 
    t.completed,
    group_concat(
        concat(
            @rn := case 
                when @seq = t.seq then @rn + 1
                when @seq := t.seq then 1
                else 1
            end, 
            ': ', 
            n.message
        ) 
        order by n.id separator '<br>' 
    ) as messages
from tickets t 
left join tickets_notes n on n.ticket_seq = t.seq
group by 
    t.seq, 
    t.subject, 
    t.location, 
    t.created, 
    t.completed
;

@seq 记录当前seq的值，@rn代表行号，每次从1重新开始seq 更改。