mysql - 我正在尝试在 mysql 中进行动态透视

Question

我正在尝试在 mysql 中进行动态数据透视，但它说我有语法错误，我该如何纠正这个错误？:

MySql 7

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN subject = "',
      subject,'"  AND ', 
           (CASE WHEN media IS NOT NULL 
           THEN CONCAT("media = ",media) 
           ELSE media IS NULL END),
      ' THEN 1 ELSE 0 end) AS ',
      subject, IFNULL(media,'')

    )
  )
INTO @sql
FROM
  cs_media_simonline;

SET @sql = CONCAT('SELECT user_id, ', @sql, ' 
                  FROM cs_media_simonline 
                   GROUP BY user_id');

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

这是错误:

'.0000000,SUM(CASE WHEN subject = "MATEMATICA" AND media = 0.0000000 THEN 1 ELSE' row 1

Answer 1

当您尝试将其用作列别名时，您的subject 似乎导致了语法错误。您不能使用数字常量作为列别名:

mysql> select 'test' as 123.000;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '123.000' at line 1

因此您应该引用 subject 的值，这样它就不会这样做。

我建议使用builtin QUOTE() function ，如果该值是包含撇号的字符串，它将处理引用，甚至处理转义。

SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN subject = ',
      QUOTE(subject),'  AND ', 
           (CASE WHEN media IS NOT NULL 
           THEN CONCAT('media = ',media) 
           ELSE 'media IS NULL' END),
      ' THEN 1 ELSE 0 end) AS ',
      QUOTE(subject), IFNULL(media,'')

    )
  )
INTO @sql
FROM
  cs_media_simonline;