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屋!