GROUP_CONCAT mysql语句错误 [英] GROUP_CONCAT mysql statement error

问题描述

我已经尝试通常是行到列，后跟在此处带有查询语句：

I have tried mysql ststement for dinamically rows to column by followed here with query statement:

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查询有什么问题

I'm not familiar with sql statement like this, and what's wrong with that SQL QUERY

谢谢...

推荐答案

如果您查看代码（并放大）：

If you look at your code (and zoom in):

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

您会看到 _ 和，是黑色的，而在字符串中它们应该是红色的。乌拉圭回合字符串破在那里。因此，您需要使用''来对单引号进行转义：

you will see that _ and , are black, while they should be red as part of the string. That means your string ist "broken" there. So you need to escape the single quotes with '':

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

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

Or use double quotes for strings that contain single quotes:

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"
    );

现在整个字符串为红色，因为它应该是：-）

Now the complete string is red as it should be :-)

http://rextester.com/SLMU41976

这篇关于GROUP_CONCAT mysql语句错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！