mysql - GROUP_CONCAT mysql语句错误

Question

我已经尝试过 mysql ststement dinamically rows to column关注 here带有查询语句:

SET @sql = NULL;
SELECT 
GROUP_CONCAT(DISTINCT
  CONCAT(
    'MAX(CASE WHEN col = ''', 
    col,
    ''' THEN val END) as `',
    col, '`'
  )
)INTO @sql
FROM
(
   SELECT A.id_a, D.id_c id_c,
          C.students students,
          CONCAT(B.`code`, '_', A.id_a) col,
          CONCAT(D.value_m, ',', D.value_n) val
  FROM table_a A
      INNER JOIN table_d D ON A.id_a =D.id_a
      INNER JOIN table_b B ON D.id_b=B.id_b 
      INNER JOIN table_c C ON D.id_c=C.id_c
)dd;

SET @sql = CONCAT('SELECT id_c, students,', @sql, '
        FROM(
            SELECT A.id_a, D.id_c id_c,
                C.students students,
                CONCAT(B.`code`, '_', A.id_a) col,
                CONCAT(D.value_m, ',', D.value_n) val
            FROM table_a A
                INNER JOIN table_d D ON A.id_a =D.id_a
                INNER JOIN table_b B ON D.id_b=B.id_b 
                INNER JOIN table_c C ON D.id_c=C.id_c
        )dd 
        GROUP BY id_c'
    );

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

带有结果消息:

Query OK, 0 rows affected
Query OK, 1 row affected
1064 - 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 '', A.id_a) col,
            CONCAT(D.value_m, ',', D.value_n) val
        FROM table_a A    ' at line 1
1064 - 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 'MAX(CASE WHEN col = 'MAT_1' THEN val END) as `MAT_1`,MAX(CASE WHEN col = 'BIO_1'' at line 1
1243 - Unknown prepared statement handler (stmt) given to EXECUTE
1243 - Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE

我不熟悉这样的sql语句，以及那个SQL QUERY有什么问题

谢谢...

Answer 1

如果您查看代码(并放大):

SET @sql = CONCAT('SELECT id_c, students,', @sql, '
        [..]
                CONCAT(B.`code`, '_', A.id_a) col,
                CONCAT(D.value_m, ',', D.value_n) val
        [..]
        GROUP BY id_c'
    );

您将看到 _ 和 , 是黑色的，而它们作为字符串的一部分应该是红色的。这意味着你的弦在那里“断”了。所以你需要用 '' 转义单引号:

SET @sql = CONCAT('SELECT id_c, students,', @sql, '
        [..]
                CONCAT(B.`code`, ''_'', A.id_a) col,
                CONCAT(D.value_m, '','', D.value_n) val
        [..]
        GROUP BY id_c'
    );

或者对包含单引号的字符串使用双引号:

SET @sql = CONCAT('SELECT id_c, students,', @sql, "
        [..]
                CONCAT(B.`code`, '_', A.id_a) col,
                CONCAT(D.value_m, ',', D.value_n) val
        [..]
        GROUP BY id_c"
    );

现在完整的字符串应该是红色的:-)

http://rextester.com/SLMU41976