暂存

select chat_id, course_id
from chat_and_course_relation;

select c.id chat_id, c.name chat_name, c2.name course_name
from chat_and_course_relation
         left join chat c on chat_and_course_relation.chat_id = c.id
         left join course c2 on chat_and_course_relation.course_id = c2.id;


select (@i := @i + 1) as rownum, a.chat_id, a.chat_name
from (
         select c.id chat_id, c.name chat_name, c2.name course_name
         from chat_and_course_relation
                  left join chat c on chat_and_course_relation.chat_id = c.id
                  left join course c2 on chat_and_course_relation.course_id = c2.id
     ) a,
     (select @i := 0) as init;

select (@i := @i + 5) as rownum, a.chat_id, a.chat_name, group_concat(course_name) course_name
from (
         select c.id chat_id, c.name chat_name, c2.name course_name
         from chat_and_course_relation
                  left join chat c on chat_and_course_relation.chat_id = c.id
                  left join course c2 on chat_and_course_relation.course_id = c2.id
     ) a,
     (select @i := 100) as init
group by chat_id, chat_name;


select *
from chat_and_course_relation;

#################################### 开始 #######################
/*SELECT a.group_id,
       a.name,
       IF(
               IFNULL(@group_ids, (@group_ids := a.group_id)) = group_id,
               (@i := @i + 1),
               (@i := 1 AND (@group_ids := a.group_id))
           ) AS rowNo
FROM test.test a,
     (SELECT @i := 0, @group_ids := NULL) i;*/

select c.id chat_id, c.name chat_name, c2.name course_name
from chat_and_course_relation
         left join chat c on chat_and_course_relation.chat_id = c.id
         left join course c2 on chat_and_course_relation.course_id = c2.id;

select a.chat_id,
       a.course_name,
       IF(
               IFNULL(@group_ids, (@group_ids := a.chat_id)) = chat_id,
               (@i := @i + 1),
               (@i := 1 AND (@group_ids := a.chat_id))
           ) AS rowNo
from (select c.id chat_id, c.name chat_name, c2.name course_name
      from chat_and_course_relation
               left join chat c on chat_and_course_relation.chat_id = c.id
               left join course c2 on chat_and_course_relation.course_id = c2.id) a


select b.chat_id, concat('[', b.rowNo, ']', b.course_name) course_name
from (select a.chat_id,
             a.course_name,
             IF(
                     IFNULL(@group_ids, (@group_ids := a.chat_id)) = chat_id,
                     (@i := @i + 1),
                     (@i := 1 AND (@group_ids := a.chat_id))
                 ) AS rowNo
      from (select c.id chat_id, c.name chat_name, c2.name course_name
            from chat_and_course_relation
                     left join chat c on chat_and_course_relation.chat_id = c.id
                     left join course c2 on chat_and_course_relation.course_id = c2.id) a) b;

select c.chat_id, group_concat(c.course_name)
from (select b.chat_id, concat('[', b.rowNo, ']', b.course_name) course_name
      from (select a.chat_id,
                   a.course_name,
                   IF(
                           IFNULL(@group_ids, (@group_ids := a.chat_id)) = chat_id,
                           (@i := @i + 1),
                           (@i := 1 AND (@group_ids := a.chat_id))
                       ) AS rowNo
            from (select c.id chat_id, c.name chat_name, c2.name course_name
                  from chat_and_course_relation
                           left join chat c on chat_and_course_relation.chat_id = c.id
                           left join course c2 on chat_and_course_relation.course_id = c2.id) a) b ) c
group by c.chat_id;