SQL statement for simplifying MySQL from view to query

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;

Note: Count (case when severity = 1 then 1 else null end) is used to display records whose count is 0.

The preceding simplified SQL statement is used to query a tape:

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;