Mysql：在sqlfiddle中工作的数据透视表，但在phpmyadmin中不工作 [英] Mysql: pivot table working in sqlfiddle but not working in phpmyadmin

问题描述

我有一个在sqlfiddle中工作的数据透视表代码，当我将它引回到我的数据库phphyadmin时，似乎无法使查询工作。

i have a pivot table code that worked in the sqlfiddle, I can't seem to get the query working when I bring it back onto my database in phpmyadmin.

这是我的sqlfiddle。

当我在本地的phpmyadmin中运行它，查询变成如下所示：

when I run it in my localHost phpmyadmin, the query becomes something like below:

SET @@group_concat_max_len = 5000;# MySQL returned an empty result set (i.e. zero rows).
SET @sql = NULL;# MySQL returned an empty result set (i.e. zero rows).
SELECT GROUP_CONCAT(DISTINCT
        CONCAT('MAX(IF(hw_id = ', hw_id, ', score, -1)) AS ',
               CONCAT('`hw', hw_id, '`'))) INTO @sql
                      FROM mk;# 1 row affected.
SET @sql = CONCAT('SELECT st.id, ', @sql,
     ' FROM st LEFT JOIN mk ON st.id = mk.st_id GROUP BY st.id');
           # MySQL returned an empty result set (i.e. zero rows).
PREPARE stmt FROM @sql;# MySQL returned an empty result set (i.e. zero rows).
EXECUTE stmt;# Rows: 3
DEALLOCATE PREPARE stmt;# MySQL returned an empty result set (i.e. zero rows).

你可以看到，我也改变了group_concat_max_len ...但没有改变...

as you can see, I change the group_concat_max_len too... but nothing changed...

推荐答案

将其构建成存储过程，然后在phpmyadmin中执行。

Build it into a stored procedure, then execute that in phpmyadmin.

如果要追求失败的phpadmin，请添加 SELECT @sql; ，看看是否收到所需的查询。

If you want to pursue the failing phpadmin, add SELECT @sql; and see if you get the desired query.

这篇关于Mysql：在sqlfiddle中工作的数据透视表，但在phpmyadmin中不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！