在MySQL中如何将行转换为列？

Question

考虑以下表格“views”

user_id  _date       cnt
------------------------
1        2011-02-10  123
1        2011-02-11   99
1        2011-02-12  100
1        2011-02 13   12
2        2011-02-10   32
2        2011-02-11  433
2        2011-02-12  222
2        2011-02 13  334
3        2011-02-10  766
3        2011-02-11  654
3        2011-02-12   43
3        2011-02 13   27
...
100      2011-02-13  235

如您所见，该表格显示了每天（_date）每个用户（user_id）的页面浏览量（cnt）。我需要一个SELECT查询，以便将user_id作为列输出，使表格数据呈矩阵形式，如下所示：

_date         1    2    3 ... 100
---------------------------------
2011-02-10  123   32  766
2011-02-11   99  433  654
2011-02-12  100  222   43
2011-02-13   12  334   27     235

能否使用SELECT语句实现这个功能？

- Pr0no

---

我认为在MySQL中这是不可能的，因为它不包括“pivot”命令...你可能需要考虑将这个逻辑实现到你的应用层。 - McGarnagle

你是在寻找一个固定数量的列（针对一组固定的user_id）还是针对表中的每个user_id？ - Mosty Mostacho

一个固定的金额。有一百个用户ID。请参见OP编辑 :-) - Pr0no

这里有回答吗？https://dev59.com/x1bUa4cB1Zd3GeqPBbty - Tahbaza

Answer 1

如果你正在处理一组有限的用户ID，你可以这样做：

SELECT _date,
    SUM(CASE WHEN _user_id = 1 THEN cnt ELSE 0 END) AS user1,
    SUM(CASE WHEN _user_id = 2 THEN cnt ELSE 0 END) AS user2,
    SUM(CASE WHEN _user_id = 3 THEN cnt ELSE 0 END) AS user3,
    ...
FROM views
GROUP BY _date

虽然这更像是一种hack而不是一个好的查询，但仍然可以使用。

- benzado

---

确实，如果你正在处理大型数据集，那么可以预期会出现慢查询。不过有最佳解决方案！ - BenOfTheNorth

谢谢！然而，这个查询给了我一个错误：#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 ') AS user1, SUM(CASE WHEN user_id = 2 THEN cnt ELSE 0) AS user2, ' at line 3。你有什么建议吗？为什么会出现这种情况？ - Pr0no

@Pr0no 我漏掉了 END，现在已经修复了示例。如果你查阅了 MySQL 文档中的 CASE，你本可以自己找出这个问题的。 - benzado

Answer 2

看起来你有一个需要转换的长值列表。如果是这样，你可以使用预处理语句。你的代码将如下所示（请参见带演示的SQL Fiddle）：

CREATE TABLE Table1
    (`user_id` int, `_date` datetime, `cnt` int)
;

INSERT INTO Table1
    (`user_id`, `_date`, `cnt`)
VALUES
    (1, '2011-02-09 17:00:00', 123),
    (1, '2011-02-10 17:00:00', 99),
    (1, '2011-02-11 17:00:00', 100),
    (1, '2011-02-13 00:00:00', 12),
    (2, '2011-02-09 17:00:00', 32),
    (2, '2011-02-10 17:00:00', 433),
    (2, '2011-02-11 17:00:00', 222),
    (2, '2011-02-13 00:00:00', 334),
    (3, '2011-02-09 17:00:00', 766),
    (3, '2011-02-10 17:00:00', 654),
    (3, '2011-02-11 17:00:00', 43),
    (3, '2011-02-13 00:00:00', 27),
    (100, '2011-02-12 17:00:00', 235)
;

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when user_id = ''',
      user_id,
      '''  then cnt else 0 end) AS ''',
      user_id, ''''
    )
  ) INTO @sql
FROM Table1;

SET @sql = CONCAT('SELECT _Date, ', @sql, ' 
                  FROM table1 
                  GROUP BY _Date');

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

- Taryn