我试图加入一个错误的所有评论以仅在一个字段中显示它们，所以我做了以下代码:

SELECT
    bu.bug_id 'External Issue ID',
    clas.name 'Classification',
    pr.name 'Product',
    comp.name 'Component',
    bu.short_desc 'Summary',
    (SELECT 
        GROUP_CONCAT(CONCAT('Comment #',@rownum:=@rownum+1,': From',p.realname, ' on ', ld.bug_when,'#BR#',ld.thetext) SEPARATOR '#BR#') 
        FROM longdescs ld INNER JOIN profiles p on ld.who = p.userid, (SELECT @rownum:=0) r  
        WHERE bu.bug_id = ld.bug_id) as 'Comments',
     bu.bug_file_loc,
     p1.login_name 'Assigned to', 
     p1.realname 'Assigned to RN', 
     p2.login_name 'Reporter',
     bu.cf_cr,
     (SELECT GROUP_CONCAT(CONCAT(ft.name,':',fl.status)) from flags fl inner join flagtypes ft on fl.type_id=ft.id WHERE fl.bug_id = bu.bug_id) 'Flag',
     (SELECT GROUP_CONCAT(p.realname, ' on: ',ba.bug_when, ' modified field:', fd.description, ' replacing value: ', CASE WHEN ba.removed ='' OR ba.removed is null THEN 'NULL' ELSE ba.removed END, ' with: ',CASE WHEN ba.added ='' OR ba.added is null THEN 'NULL' ELSE ba.added END SEPARATOR '#BR#')FROM bugs_activity ba inner join profiles p on ba.who = p.userid inner join fielddefs fd on ba.fieldid = fd.id WHERE bu.bug_id = ba.bug_id) as 'History'
FROM bugs bu
    INNER JOIN profiles p1  on bu.assigned_to = p1.userid
    INNER JOIN profiles p2  on bu.reporter = p2.userid
    INNER JOIN profiles p3 on bu.qa_contact = p3.userid
    INNER JOIN products pr on bu.product_id = pr.id
    INNER JOIN components comp on bu.component_id = comp.id
    INNER JOIN classifications clas on pr.classification_id = clas.id
    INNER JOIN bugs_fulltext buft on bu.bug_id = buft.bug_id
WHERE
 pr.id=116
 AND bu.bug_status = 'RESOLVED'
 AND bu.cf_task_type = 'Defect'
LIMIT 100

这个查询给了我这样的结果:

我希望清除每一行的“commentnum”。例如，对于错误“125932”，“评论 #2”应为“评论 #1”，“评论 #3”应为“评论 #2”，依此类推。

有什么想法吗？

最佳答案

像这样为每一行尝试重置变量 @commentum :

SELECT
    bu.bug_id 'External Issue ID',
    clas.name 'Classification',
    pr.name 'Product',
    comp.name 'Component',
    bu.short_desc 'Summary',
        @rownum:=0 as x, -- Reset variable rownum to 0
    (SELECT 
        GROUP_CONCAT(CONCAT('Comment #',@rownum:=@rownum+1,': From',p.realname, ' on ', ld.bug_when,'#BR#',ld.thetext) SEPARATOR '#BR#') 
        FROM longdescs ld INNER JOIN profiles p on ld.who = p.userid
        WHERE bu.bug_id = ld.bug_id) as 'Comments',
     bu.bug_file_loc,
     p1.login_name 'Assigned to', 
     p1.realname 'Assigned to RN', 
     p2.login_name 'Reporter',
     bu.cf_cr,
     (SELECT GROUP_CONCAT(CONCAT(ft.name,':',fl.status)) from flags fl inner join flagtypes ft on fl.type_id=ft.id WHERE fl.bug_id = bu.bug_id) 'Flag',
     (SELECT GROUP_CONCAT(p.realname, ' on: ',ba.bug_when, ' modified field:', fd.description, ' replacing value: ', CASE WHEN ba.removed ='' OR ba.removed is null THEN 'NULL' ELSE ba.removed END, ' with: ',CASE WHEN ba.added ='' OR ba.added is null THEN 'NULL' ELSE ba.added END SEPARATOR '#BR#')FROM bugs_activity ba inner join profiles p on ba.who = p.userid inner join fielddefs fd on ba.fieldid = fd.id WHERE bu.bug_id = ba.bug_id) as 'History'
FROM bugs bu
    INNER JOIN profiles p1  on bu.assigned_to = p1.userid
    INNER JOIN profiles p2  on bu.reporter = p2.userid
    INNER JOIN profiles p3 on bu.qa_contact = p3.userid
    INNER JOIN products pr on bu.product_id = pr.id
    INNER JOIN components comp on bu.component_id = comp.id
    INNER JOIN classifications clas on pr.classification_id = clas.id
    INNER JOIN bugs_fulltext buft on bu.bug_id = buft.bug_id
    INNER JOIN (SELECT @rownum:=0) r  -- Join here
WHERE
 pr.id=116
 AND bu.bug_status = 'RESOLVED'
 AND bu.cf_task_type = 'Defect'
LIMIT 100

关于mysql - 枚举对 GROUP_CONCAT 的评论，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/32829229/