Question

审议以下表格“概览”

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

如你所知,该表每天对每个用户(用户_id)进行页面访问。 I m 寻找能够输出用户的电离层电离层电离层电离层电离层电离层电离层电离层的电离层电离层电离层电离层电离层电离层电离层电离层。

_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

这是否与选任考试委员会的声明有关?

Answer 1

如果你重新处理一套有限的用户识别器,你可以这样做:

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

然而,它比一个好奇多。

Answer 2

它期望你们有你们想要改变的长串价值观。如果是这样的话,你可以使用。你的法典将照此办理(见 SQL Fiddle with Demo):

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;

友情链接