我有这张桌子:
CREATE TABLE IF NOT EXISTS `usage` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`host_id` int(10) unsigned NOT NULL,
`time_id` int(10) unsigned NOT NULL,
`state` enum('LinuxTU','LinuxExt','View','Browser','Idle','Offline') CHARACTER SET latin1 NOT NULL DEFAULT 'Offline',
PRIMARY KEY (`id`),
KEY `host_id` (`host_id`),
KEY `time_id` (`time_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=12998 ;
该表存储不同计算机的状态信息。
我实际上可以得到这个。哪个存储我需要的值,但没有正确的格式:
SELECT time_id, state, COUNT(state) statecount
FROM `usage` u
GROUP BY time_id, state
结果:
time_id state statecount
7 LinuxTU 20
7 LinuxExt 11
7 View 51
7 Browser 5
7 Idle 67
7 Offline 83
8 LinuxTU 22
8 LinuxExt 10
8 View 55
8 Browser 4
8 Idle 66
8 Offline 80
我想像这样得到一个带有计数状态值的矩阵:
time_id LinuxTU LinuxExt View Browser Idle Offline
7 20 11 51 5 67 83
8 22 10 55 4 66 80
我怎么能得到这个?
最佳答案
好的,数据透视表是关键。也许有没有子查询的查询,但我没有找到它。而且此查询对于列更改不灵活。
但这有效...
SELECT time_id,
SUM(IF(state='LinuxTU', statecount, NULL)) AS LinuxTU,
SUM(IF(state='LinuxExt', statecount, NULL)) AS LinuxExt,
SUM(IF(state='View', statecount, NULL)) AS View,
SUM(IF(state='Browser', statecount, NULL)) AS Browser,
SUM(IF(state='Idle', statecount, NULL)) AS Idle,
SUM(IF(state='Offline', statecount, NULL)) AS Offline
FROM (
SELECT time_id, state, COUNT(state) statecount
FROM `usage` u
GROUP BY time_id, state
) AS subtab
GROUP BY time_id
关于mysql - 如何将多行转换为一?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/28563420/