php - 使用php在mysql中将行显示为列

我有桌子

 create table course
 (
    id int not null auto_increment primary key,
    course_name varchar(50),
    level varchar(20),
    course_detail varchar(250)
 );



 insert into course(course_name,level,course_detail) values
 ('c','beginner','datatypes'),
 ('c','beginner','basics'),
 ('c','beginner','variables'),
 ('c','intermediate','functions'),
 ('c','intermediate','loops'),
 ('c','advanced','stacks'),
 ('c','advanced','queues'),

 ('java','beginner','datatypes'),
 ('java','beginner','basics'),
 ('java','beginner','variables'),
 ('java','intermediate','eventlisteners'),
 ('java','intermediate','exeptions'),
 ('java','advanced','jdbc'),
 ('java','advanced','j2ee');

输出应如下所示：

course_name  beginner   intermediate     advanced
-------------------------------------------------
  c          basics     functions        stacks
  c          datatypes  loops            queues
  c          variables
  java       basics     eventlisteners   jdbc
  java       datatypes  exceptions       j2ee
  java       variables

最佳答案

根据我上面的编辑，假定MyISAM或id是代理PK。

SELECT course_name
     , MAX(CASE WHEN level = 'beginner' THEN course_detail END) beginner
     , MAX(CASE WHEN level = 'intermediate' THEN course_detail END) intermediate
     , MAX(CASE WHEN level = 'advanced' THEN course_detail END) advanced
  FROM
     (
       SELECT x.*
            , COUNT(*) rank
         FROM course x
         JOIN course y
           ON y.course_name = x.course_name
          AND y.level = x.level
          AND y.id <= x.id
        GROUP
           BY course_name
            , id
     ) a
 GROUP
    BY course_name,rank;

关于php - 使用php在mysql中将行显示为列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25929372/