MySQL:类似数据透视的查询结果

Question

给定以下查询，该查询生成学生列表和他们被分配到的队列:

SELECT mdl_user.lastname, mdl_user.firstname, mdl_cohort.name
FROM mdl_cohort
INNER JOIN mdl_cohort_members ON mdl_cohort_members.cohortid = mdl_cohort.id
INNER JOIN mdl_user ON mdl_cohort_members.userid = mdl_user.id
ORDER BY mdl_user.lastname, mdl_cohort.name

是否有可能产生一个结果，显示每个队列名称一次，最好是作为列名，每个分配到该队列的学生都列在它下面？像这样:

Engineering Cohort    Administrative Cohort    IT Cohort
------------------    ---------------------    ---------
John Doe              Jane Smith               Jane Doe
Jane Smith            John Smith               John Doe
Dan Jones             Dana Jones               Dana Jones

Answer 1

这可以编写脚本。纯基于 SQL 的方法非常难看。下面的方法将结果加载到一个表中(每个计数器从一个递增)然后连接在一起。最后的联合会模拟完整的外部联接，以确保不会遗漏任何群组。

set @admin:=0;
set @eng:=0;
set @it:=0;

drop table if exists administrative;
create table administrative as
      select (@admin := @admin + 1) as counter
          , cohort
       from (    select concat(mdl_user.firstname, ' ', mdl_user.lastname) as cohort
                   from mdl_cohort
              inner join mdl_cohort_members on (mdl_cohort_members.cohortid = mdl_cohort.id)
              inner join mdl_user on (mdl_cohort_members.userid = mdl_user.id)
                  where mdl_cohort.name='Administrative Cohort' 
               order by mdl_user.lastname, mdl_user.firstname) as admin;

drop table if exists engineering;
create table engineering as
      select (@eng := @eng + 1) as counter
          , cohort
       from (    select concat(mdl_user.firstname, ' ', mdl_user.lastname) as cohort
                   from mdl_cohort
             inner join mdl_cohort_members on (mdl_cohort_members.cohortid = mdl_cohort.id)
             inner join mdl_user on (mdl_cohort_members.userid = mdl_user.id)
                  where mdl_cohort.name='Engineering Cohort' 
               order by mdl_user.lastname, mdl_user.firstname) as eng;

drop table if exists it;
create table it as
      select (@it := @it + 1) as counter
           , cohort
        from (    select concat(mdl_user.firstname, ' ', mdl_user.lastname) as cohort
                    from mdl_cohort
              inner join mdl_cohort_members on (mdl_cohort_members.cohortid = mdl_cohort.id)
              inner join mdl_user on (mdl_cohort_members.userid = mdl_user.id)
                   where mdl_cohort.name='IT Cohort' 
                order by mdl_user.lastname, mdl_user.firstname) as it;

select admin.cohort as `Administrative Cohorts`,
       eng.cohort   as `Engineering Cohorts`,
       it.cohort    as `IT Cohorts`
  from administrative admin
 left join engineering eng on(eng.counter=admin.counter)
 left join it on (it.counter=admin.counter)
 union
select ''         as `Administrative Cohorts`,
       eng.cohort as `Engineering Cohorts`,
       it.cohort  as `IT Cohorts`
  from engineering eng
 left join it on (it.counter=eng.counter)
 where not exists (select 1
                     from administrative 
                   where counter=eng.counter)
union
select ''         as `Administrative Cohorts`,
       ''         as `Engineering Cohorts`,
       it.cohort  as `IT Cohorts`
  from it
 where not exists (select 1
                     from administrative
                    where counter=it.counter)
   and not exists (select 1
                     from engineering
                    where counter=it.counter);