暂存

?

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;