在MySQL中选择行作为列?

Consider the following table "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

As you can see, the table holds pageviews (cnt) per day (_date) per user (user_id). I'm looking for a SELECT query that will output the user_ids as columns so the table-data will be in matrix form, as follows:

如您所见，该表保存了每个用户(user_id)每天的pageviews (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

Is this possible to do with a SELECT statement?

这可能与SELECT语句有关吗?

2 个解决方案

#1

If you're dealing with a finite set of user IDs, you could do something like this:

如果你处理的是有限的用户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

It's more of a hack than a good query, though.

不过，与其说这是一个好的查询，不如说是一个黑客。

#2

It looks like you have an long list of values that you want to transform. If that is the case then you can use prepared statements. Your code will look like this (see SQL Fiddle with Demo):

看起来您有一长串要转换的值列表。如果是这样，那么您可以使用准备语句。您的代码将是这样的(参见SQL小提琴与演示):

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;

#1