MySQL 交叉表结果

Question

寻找有关从 MySQL 查询返回交叉表结果的一些帮助，我过去使用过 MS Access 和数据透视表，效果很好。我正在转向 MySQL 并且需要获得相同的结果。我找到了mysql pivot/crosstab query这正是我想要实现的目标，但我的表中似乎出现了错误。 (SO 示例中 SQLFiddle 的链接出现错误)

SET SESSION group_concat_max_len = 10000;
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      ' GROUP_CONCAT((CASE Class_Name when ', CHAR(39), 
      ClassName, CHAR(39),
      ' then ', CHAR(39), DateCompleted, CHAR(39), ' else NULL END)) AS     Completed',
      ClassName
    )
  ) INTO @sql
FROM EnrollmentsTbl;

架构:

SET NAMES 'UTF8';


CREATE TABLE `EnrollmentsTbl` (
`AutoNum` INTEGER PRIMARY KEY,
`UserName` VARCHAR(50),
`SubmitTime` DATETIME,
`ClassName` VARCHAR(50),
`ClassDate` DATETIME,
`ClassTime` VARCHAR(50),
`Enrolled` BOOLEAN,
`WaitListed` BOOLEAN,
`Instructor` VARCHAR(50),
`DateCompleted` DATETIME,
`Completed` BOOLEAN,
`EnrollmentsMisc` VARCHAR(50),
`Walkin` BOOLEAN
) CHARACTER SET 'UTF8';

INSERT INTO         `EnrollmentsTbl`(`AutoNum`,`UserName`,`SubmitTime`,`ClassName`,`ClassDate`,    `ClassTime`,`Enrolled`,`WaitListed`,`Instructor`,`DateCompleted`,`Completed`,`EnrollmentsMisc`,`Walkin`)
VALUES(1,'John',NULL,'MDC (Intro)','2004-06-27 00:00:00',NULL,TRUE,FALSE,'Phil','2004-06-27 00:00:00',TRUE,NULL,FALSE),
  (2,'Bob',NULL,'MDC (Intro)','2004-06-27 00:00:00',NULL,TRUE,FALSE,'Phil','2004-06-27 00:00:00',TRUE,NULL,FALSE),
  (3,'Robert',NULL,'MDC (Intro)','2004-06-27 00:00:00',NULL,TRUE,FALSE,'Phil','2004-06-27 00:00:00',TRUE,NULL,FALSE),
  (4,'John','2010-08-04 06:11:10','HIPAA(Employee)','2010-08-04 00:00:00','6:12 AM',TRUE,FALSE,'On-line','2010-08-04 06:11:10',TRUE,NULL,FALSE),
  (5,'Debbie',NULL,'MDC (Intro)','2003-04-19 14:53:55',NULL,TRUE,FALSE,'devore','2003-04-19 14:53:55',TRUE,NULL,FALSE),
  (6,'Jeff',NULL,'MDC (Intro)','2003-03-29 14:26:23',NULL,TRUE,FALSE,'','2003-03-29 14:26:23',TRUE,NULL,FALSE),
  (7,'Tom',NULL,'Firehouse (Incident)','2004-07-13 00:00:00',NULL,TRUE,FALSE,'Shannon','2004-07-13 00:00:00',TRUE,NULL,FALSE),
  (8,'Rhonda',NULL,'Firehouse (Incident)','2004-07-13 00:00:00',NULL,TRUE,FALSE,'arobe','2004-07-13 00:00:00',TRUE,NULL,FALSE),
  (9,'Jeff',NULL,'Firehouse (Incident)','2004-07-13 00:00:00',NULL,TRUE,FALSE,'arobe','2004-07-13 00:00:00',TRUE,NULL,FALSE),
  (10,'Patrick',NULL,'Firehouse (Incident)','2004-07-13 00:00:00',NULL,TRUE,FALSE,'arobe','2004-07-13 00:00:00',TRUE,NULL,FALSE),
  (11,'Donnie',NULL,'Firehouse (Incident)','2004-07-10 00:00:00',NULL,TRUE,FALSE,'feiertag','2004-07-10 00:00:00',TRUE,NULL,FALSE),
  (12,'Andy',NULL,'Firehouse (EMS)','2004-07-10 00:00:00',NULL,TRUE,FALSE,'feiertag','2004-07-10 00:00:00',TRUE,NULL,FALSE),
  (13,'Brian',NULL,'Firehouse (Incident)','2004-07-17 00:00:00',NULL,TRUE,FALSE,'Paul','2004-07-17 00:00:00',TRUE,NULL,FALSE),
  (14,'Jane',NULL,'Firehouse (EMS)','2004-07-17 00:00:00',NULL,TRUE,FALSE,'Paul','2004-07-17 00:00:00',TRUE,NULL,FALSE),
  (15,'Richard',NULL,'Firehouse (EMS)','2004-07-17 00:00:00',NULL,TRUE,FALSE,'Paul','2004-07-17 00:00:00',TRUE,NULL,FALSE),
  (16,'Dale',NULL,'Firehouse (EMS)','2004-07-17 00:00:00',NULL,TRUE,FALSE,'Paul','2004-07-17 00:00:00',TRUE,NULL,FALSE),
  (17,'Stinky','2016-06-29 17:17:19','FireApp (Assessment Only)','2016-07-18 00:00:00','1830',TRUE,FALSE,NULL,NULL,FALSE,NULL,FALSE),
  (18,'Janet','2016-06-30 14:02:05','MDC (On-Line)','2016-06-30 00:00:00','2:02 PM',TRUE,FALSE,'On-line','2016-06-30 14:02:05',TRUE,NULL,FALSE);

这是我失败的 SQLFiddle 示例:http://sqlfiddle.com/#!9/0c4c2/3

更新:这是返回的 @sql 和错误:@sqlSELECT AutoNum, UserName, GROUP_CONCAT((CASE Class_Name when 'MDC (Intro)' then '2004-06-27 00:00:00'else NULL END)) AS CompletedMDC (Intro), GROUP_CONCAT((CASE Class_Name when 'HIPAA(员工)'，然后 '2010-08-04 06:11:10'，否则 NULL END)) AS 已完成HIPAA(员工)，GROUP_CONCAT((CASE Class_Name，当 'MDC(介绍)' 时，然后 '2003-04-19 14 :53:55'else NULL END)) AS CompletedMDC (Intro), GROUP_CONCAT((CASE Class_Name when 'MDC (Intro)' then '2003-03-29 14:26:23'else NULL END)) AS CompletedMDC (Intro) ), GROUP_CONCAT((CASE Class_Name when 'Firehouse (Incident)' then '2004-07-13 00:00:00'else NULL END)) AS CompletedFirehouse (Incident), GROUP_CONCAT((CASE Class_Name when 'MDC (On-Line) )' then '2016-06-30 14:02:05'else NULL END)) AS CompletedMDC (On-Line) FROM enrollmentstbl GROUP BY AutoNum, UserName

记录数:1；执行时间:1ms 查看执行计划链接
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行 '(Intro), GROUP_CONCAT((CASE Class_Name when 'HIPAA (Employee)' then '2010-08-04 ' at line 1

Answer 1

问题是，在链接主题中，评估的字段是数字(node_id)，而在您的情况下，它是文本(类名)。但是在您的代码中，您没有用单引号或双引号将来自该字段的值括起来，因此 MySQL 无法真正解释它们，因此会出现错误消息。

我将 char(39) 调用添加到创建 group_concat() 调用的 SQL 中。 Char(39) 是撇号(单引号)。不幸的是，我无法访问 sqlfiddle 来检查它现在是否有效。但在尝试从 @sql 创建准备好的语句之前执行 select @sql 命令，您可以自己检查和测试生成的 sql 语句。

您可能还必须第二次用单引号将类名引起来。

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT((CASE Class_Name when ', CHAR(39), 
      ClassName, CHAR(39),
      ' then DateCompleted else NULL END)) AS Completed',
      ClassName
    )
  ) INTO @sql
FROM EnrollmentsTbl;

更新

第二个问题是您的类名字段值包含空格和其他非常规字符(例如括号)，这些字符会对您的字段名称别名造成严重破坏。

例如，您的 sql 中有以下别名:

...AS CompletedHIPAA (Employee)...

您需要用反引号字符 (`) 将此类别名括起来才能工作:

...`AS CompletedHIPAA (Employee)`...

修改 SQL 以包含反引号:

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT((CASE Class_Name when ', CHAR(39), 
      ClassName, CHAR(39),
      ' then DateCompleted else NULL END)) AS `Completed',
      ClassName,'`'
    )
  ) INTO @sql
FROM EnrollmentsTbl;