MySql 查询动态地将行转换为按 DATA_FORMAT 分组的列

Question

我的 MySQL 5.5 数据库中有两个表:

CREATE TABLE `t_user` (
  `USER_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`USER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `t_user_activity_log` (
  `USER_ID` int(10) unsigned NOT NULL,
  `ACTIVITY_DATE` datetime NOT NULL,
  `ACTIVITY_COUNT` int(10) unsigned NOT NULL,
  PRIMARY KEY (`USER_ID`,`ACTIVITY_DATE`),
  CONSTRAINT `FK_t_user` FOREIGN KEY (`USER_ID`) REFERENCES `t_user` (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我需要得到按时间段分组的结果，例如:

----------------------------------------------
| Period     | User1 | User2 | ***** | UserN |
----------------------------------------------
| 22.02.2012 |   12  |   12  |   x   |   x   |
----------------------------------------------
| 23.02.2012 |   7   |   3   |   x   |   x   |
----------------------------------------------
| 24.02.2012 |   0   |   0   |   0   |   0   |
----------------------------------------------

周期是可变的(小时、周、月、年)。应该可以通过 FROM 和 TO 日期限制查询，如果没有找到日期记录 - 它应该仍然在结果中可见。应从 in() 列表中选择用户。

有可能吗？

Answer 1

你可以做类似的事情，显然你会从你的实际用户列表中用你的服务器语言生成这个查询。

SELECT 
t.ACTIVITY_DATE,
SUM(IF(t.USER_ID = 1, t.activity_count, 0)) as 'user1',
SUM(IF(t.USER_ID = 2, t.activity_count, 0)) as 'user2'

FROM (
SELECT t_user.USER_ID, 
    t_user.USER_NAME, 
    t_user_activity_log.ACTIVITY_DATE, 
    SUM(t_user_activity_log.ACTIVITY_COUNT) as activity_count
FROM t_user_activity_log 
INNER JOIN t_user ON t_user_activity_log.USER_ID = t_user.USER_ID

GROUP BY t_user.USER_ID, t_user_activity_log.ACTIVITY_DATE
) as t

GROUP BY t.USER_ID, t.ACTIVITY_DATE