mysql - 将行转为列 - 评级数据库

Question

我有下表:

评分

User-ID     ISBN    Book-Rating
244662  0373630689  3
19378   0812515595  2
238625  0441892604  5
180315  0140439072  1
242471  3548248950  1

书籍
ISBN 书名 书名 作者 出版年份 出版商
0393000753 清算 May Sarton 1981 W W Norton

使用 MySQL，我想创建一个如下所示的表:

----- 用户1 用户2 用户3 ...
ISBN1 评级 11 NaN 南
ISBN2 NaN 评级21 评级23
ISBN3 评级 31 NaN NaN
...

我已经学会了如何对固定数量的列执行此操作 here 。但是如何将行转换为列呢？我尝试过类似的事情

将 BX-Book-Ratings-Extend View 创建为 (
选择
ISBN,
当Book-Rating= "1"时，1 结束为User-ID，
当Book-Rating= "2"时，则 2 以User-ID 结尾，
当Book-Rating= "3"时，则 3 以User-ID 结尾，
当Book-Rating= "4"时，4 结束为User-ID，
当Book-Rating= "5"时，则 5 结尾为User-ID
来自BX-Book-Ratings
)

这不起作用 - View 是空的...我也尝试采用 another 中的解决方案线程，但不断收到我无法识别的语法错误:

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when `Book-Rating` = ''',
      `User-ID`,
      ''' then `Book-Rating` end),0) AS ',
      `User-ID`
    )
  ) INTO @sql
FROM
  `BX-Book-Ratings`;
SET @sql = CONCAT('SELECT ISBN, ', @sql, ' 
                  FROM BX-Book-Ratings 
                   GROUP BY ISBN');

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

数据集为here .

感谢您的建议!

Answer 1

您对动态 SQL(准备/执行)的第二次尝试已接近成功。
尝试这个查询:

SET @sql = NULL;

SELECT Concat(
         'SELECT ISBN, ',
         Group_Concat( x SEPARATOR ','),
         ' FROM ratings GROUP BY ISBN '
       )
INTO @sql
FROM (
  SELECT Concat('Max( CASE WHEN User_ID = ', 
                User_ID, 
               ' THEN Book_Rating END ) As User_', 
               User_ID 
         ) As x
  FROM ratings 
  GROUP BY User_ID
  ORDER BY User_ID
) x;

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

演示:http://rextester.com/GLJV4738

如果一个用户可以对一本书有多个评分，则将 max 替换为 sum。

<小时/>

不幸的是，由于MySql limits，该解决方案不适用于超过数千个用户(列)。

• MySQL 对每个表有 4096 列的硬限制(因此查询结果集也不能超过 4096 列)
• MySQL 表的最大行大小限制为 65,535 字节(更多信息请参阅 here)，因此连续 150.000 个用户的行大小肯定必须超过 65 k 字节