mysql - SO 对动态旋转原因的回答 'Error in SQL Synthax'

Question

我已经尝试了有关动态旋转的所有合适答案，但在立即执行代码时总是会出现以下错误:

QueryException: 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 'SET @sql = CONCAT('SELECT day , ', @sql, ' FROM my_table GROUP BY day'); ' at line 6

我正在安装了 MYSQL 5.1.63 的 Amazon AWS 上工作，我准备的语句是以下代码:

SET @sql = NULL;
SELECT GROUP_CONCAT(
    DISTINCT CONCAT('MAX(case when user = ''', user, ''' then hours end)
    AS ', user))
    INTO @sql
    FROM my_table;

SET @sql = CONCAT('SELECT day , ', @sql, '
    FROM my_table
    GROUP BY day');

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

我的观点如下:

day     user    task    hours
monday  user1   wash    3
monday  user2   clean   2
monday  user3   iron    4
tuesday user1   clean   4
tuesday user2   iron    1
tuesday user3   wash    3

并且应该动态地转向此:

day     user1  user2  user3 
monday  3      2      4      
tuesday 4      1      3      

我尝试一点一点地执行查询，这样错误就出现在“准备”行中:

QueryException: 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 '1 FROM eee_alltime GROUP BY mon' at line 7 Query is: sql : 'PREPARE stmt FROM @sql;

有人知道我做错了什么吗？

我已经在同一数据库的表(而不是 View )上尝试过代码，它也不起作用，并且我无法在 Fiddle 上重现错误。

Answer 1

我发现“用户”列中的一个条目是“1”，从而产生了错误。

@avisheks:我能够重现 fiddle 中的错误并通过在 select 语句中添加一个 where 子句来解决，因为我只想要列出包含“user”的用户名:

SET @sql = NULL;
SELECT GROUP_CONCAT(
    DISTINCT CONCAT('MAX(case when user = ''', user, ''' then hours end)
    AS ', user))
    INTO @sql
    FROM my_table WHERE user LIKE '%user%';

SET @sql = CONCAT('SELECT day , ', @sql, '
    FROM my_table
    GROUP BY day');

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

看看fiddle 。非常感谢 avisheks 和 bluefeet 让我使用 fiddle 来重现错误!