mysql每日一题0723----- 关联查询然后行转列统计

hope you can enjoy 😄 !

CREATE TABLE T0723A
(
ID INT,
机房 VARCHAR(20)
);
CREATE TABLE T0723B 
(
ID INT,
机房ID INT,
主机名称 VARCHAR(20),
主机状态 INT
);
 
INSERT INTO T0723A VALUES (1,'机房A');
INSERT INTO T0723A VALUES (2,'机房B');
INSERT INTO T0723A VALUES (3,'机房C');
 
INSERT INTO T0723B VALUES (1,1,'主机A',1);
INSERT INTO T0723B VALUES (2,1,'主机B',1);
INSERT INTO T0723B VALUES (3,1,'主机C',2);
INSERT INTO T0723B VALUES (4,2,'主机D',0);
INSERT INTO T0723B VALUES (5,2,'主机E',1);

• solution

关联查询之后，进行行转列统计操作

select a.机房,
count(case when 主机状态=0 then 1 else null end) as "0",
count(case when 主机状态=1 then 1 else null end) as "1",
count(case when 主机状态=2 then 1 else null end) as "2"

from t0723a  a left join
t0723b b
on b.机房ID = a.ID 
group  by  1;