mysql從視圖view簡化到帶子查詢的sql

DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
`log_id` bigint(20) NOT NULL auto_increment,
`device_ip` varchar(23) NOT NULL,
`severity` tinyint(1) NOT NULL,
PRIMARY KEY (`log_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE VIEW `k3` AS SELECT device_ip,count(CASE WHEN severity=3 THEN 1 ELSE NULL END) as s3 FROM log where 1=1 group by device_ip;

CREATE VIEW `k2` AS SELECT device_ip,count(CASE WHEN severity=2 THEN 1 ELSE NULL END) as s2 FROM log where 1=1 group by device_ip;

CREATE VIEW `k1` AS SELECT device_ip,count(CASE WHEN severity=1 THEN 1 ELSE NULL END) as s1 FROM log where 1=1 group by device_ip;

select k3.device_ip,k3.s3,k2.s2,k1.s1 from k3,k2,k1 where k3.device_ip=k2.device_ip and k2.device_ip=k1.device_ip order by k3.s3 desc,k2.s2 desc,k1.s1 desc;

說明：count(CASE WHEN severity=1 THEN 1 ELSE NULL END)是為了讓count為0的記錄也顯示出來。

以上簡化為一個帶子查詢的sql：

select k3.device_ip,k3.s3,k2.s2,k1.s1 from (SELECT device_ip,count(CASE WHEN severity=3 THEN 1 ELSE NULL END) as s3 FROM log where 1=1 group by device_ip) as k3,(SELECT device_ip,count(CASE WHEN severity=2 THEN 1 ELSE NULL END) as s2 FROM log where 1=1 group by device_ip) as k2,(SELECT device_ip,count(CASE WHEN severity=1 THEN 1 ELSE NULL END) as s1 FROM log where 1=1 group by device_ip) as k1 where k3.device_ip=k2.device_ip and k2.device_ip=k1.device_ip order by k3.s3 desc,k2.s2 desc,k1.s1 desc;