MySQL CrossTab 结果包含非不同列

Question

两部分问题:

1. 在 MySql 中生成交叉表结果时，Distinct 调用似乎不起作用...或者我遗漏了其他内容？我在几列中得到相同的 ClassName 。即“MDC(简介)”。

2. : SQLFiddle 中的现有示例产生结果(虽然不正确)，但是当移动到托管 MySql 时，它会失败并出现错误:#1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以了解在 'el 附近使用的正确语法
FROM EnrollmentsTblx
GROUP BY AutoNum' 在第 1 行

SQL:

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


SET @sql = CONCAT('SELECT AutoNum, UserName, ', @sql, ' 
              FROM EnrollmentsTbl 
              GROUP BY AutoNum, UserName');

SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

方案:

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,'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);

fiddle 屏幕截图:

我在 Access DB 中将其作为 SQL 运行:

TRANSFORM DateCompleted
SELECT UserName
FROM EnrollmentsTbl
GROUP BY UserName
ORDER BY UserName
PIVOT ClassName

期望的结果如下所示:

UserName    MDC (Intro)     HIPAA (Employee)    Firehouse (Incident)
--------    -----------     ----------------    --------------------
John        6-27-2004       10-4-2006           8-6-2005
Bob         6-27-2004                           
Robert      6-27-2004                           8-6-2005
Debbie      4-19-2003
Jeff                                            11-25-2006
Tom                         7-13-2004
Janet                       11-25-2006

Answer 1

您的字符串在每个单引号之前都有一个转义的 \。

为了证明这一点，我将其转储到文本编辑器(MySQL Workbench)中，在创建表后它是显而易见的。我执行了 ctrl-H 查找和替换(\' 到 a ')，并且能够执行您准备好的 stmt 并获取结果集。

这是在系统(或 shell 或各种编程语言)之间移动数据时的常见问题。我还注意到数据末尾有一些 \n 。

此外，DISTINCT 适用于所有列(而不是单独的每一列)。

两列输出如此不同，很容易导致以下结果:

class    student
-------  ----------
ABC      Drew
ABC      Barlay

查看mysql replace()函数，或者:

SELECT 
  GROUP_CONCAT(
    DISTINCT
    CONCAT(
      ' GROUP_CONCAT((CASE ClassName when \'',  
      ClassName,  
      '\' then \'',DateCompleted,'\' else NULL END)) AS `',
      ClassName, '`'
    )
  ) INTO @sql
FROM EnrollmentsTbl;
SET @sql = CONCAT("SELECT AutoNum, UserName, ", @sql, " FROM EnrollmentsTbl GROUP BY AutoNum, UserName");

select @sql;

渲染:

SELECT AutoNum, UserName,  GROUP_CONCAT((CASE ClassName when 'MDC (Intro)' then '2004-06-27 00:00:00' else NULL END)) AS `MDC (Intro)`, GROUP_CONCAT((CASE ClassName when 'HIPAA (Employee)' then '2010-08-04 06:11:10' else NULL END)) AS `HIPAA (Employee)`, GROUP_CONCAT((CASE ClassName when 'MDC (Intro)' then '2003-04-19 14:53:55' else NULL END)) AS `MDC (Intro)`, GROUP_CONCAT((CASE ClassName when 'MDC (Intro)' then '2003-03-29 14:26:23' else NULL END)) AS `MDC (Intro)`, GROUP_CONCAT((CASE ClassName when 'Firehouse (Incident)' then '2004-07-13 00:00:00' else NULL END)) AS `Firehouse (Incident)`, GROUP_CONCAT((CASE ClassName when 'MDC (On-Line)' then '2016-06-30 14:02:05' else NULL END)) AS `MDC (On-Line)` FROM EnrollmentsTbl GROUP BY AutoNum, UserName
-- Drew used above

对于转义序列方面来说就像一个魅力。

要了解为什么在 concat() 上使用双引号，那么，使用单引号会导致 @sql 的 prev 值具有单引号引号使用 \ 转义，因此可能会导致系统出现 1064 错误。所以，如果不是把绳子放在一起的第二部分，你可能会无家可归。

始终暂停并查看步骤之间的字符串，以了解事物处于什么状态，以及哪些内容取消了先前的步骤。

现在讨论您的 DISTINCT 数据问题(这是堆栈上的新问题，而不是这里)