sql - SQL：用于两级聚合的 INNER JOIN

Question

我的问题是关于返回课程列表的 SQL 查询（见图）。

而不是class_id，我想打印类的名称。类名存储在 table 中classes。我已经尝试过INNER JOIN，但不幸的是我还没有找到解决方案。

select 
    group_concat(ids) as ids,
    group_concat(class_id) as class_ids,
    teacher, name, weekday_hours
from
    (select 
         l.class_id, l.teacher_id, l.name,
         group_concat(l.weekday, ':', l.hour order by l.weekday, l.hour) as weekday_hours,
         group_concat(l.id order by l.id) as ids
     from 
         lessons l
     group by 
         l.class_id, l.teacher_id, l.name) l
group by 
    teacher, name, weekday_hours;

标签： sqlgroup-byinner-join

Answer 1

你想join在子查询中，然后name像你一样处理class_id：

select group_concat(ids) as ids,
       group_concat(class_name) as class_names,
       teacher, name, weekday_hours
from (select l.class_id, c.name as class_name,
             l.teacher_id, l.name,
             group_concat(l.weekday, ':', l.hour order by l.weekday, l.hour) as weekday_hours,
             group_concat(l.id order by l.id) as ids
      from lessons l join
           classes c
           on l.class_id = c.id
      group by l.class_id, c.name, l.teacher_id, l.name
     ) l
group by teacher, name, weekday_hours;