写的不错
SELECT name ,
MAX(CASE type WHEN ‘数学’ THEN score ELSE 0 END ) math,
MAX(CASE type WHEN ‘英语’ THEN score ELSE 0 END ) English ,
MAX(CASE type WHEN ‘语文’ THEN score ELSE 0 END ) Chinese
FROM stu
GROUP BY name
https://blog.csdn.net/rainyspring4540/article/details/50231435

这个case when的写法一步一步演变

1
2
3
4
5
6
7
8
9
10

SELECT version,group_concat(type,':',score) step0,
               group_concat(type,':',CASE type WHEN  '0'  THEN score ELSE 0 END) step1, 
                group_concat(CASE type WHEN  '0'  THEN score ELSE 0 END) step2,        
                MAX(CASE type WHEN '0' THEN score ELSE 0 END) step3

        FROM (select client_file_version version, machine_type type, count(*) score
              from atc_machine
              group by client_file_version, machine_type) table_a
        GROUP BY version;

结果如下

如果想统计1和2的可以这么写
SUM(CASE WHEN type in(‘1’,’2’) THEN score ELSE 0 END) yier,

这篇没咋看
https://www.cnblogs.com/weix-l/p/7521278.html

乱入讲join性能的
因为之前写了一个复杂的(机器类型:总数) left join (机器类型:具有心跳机器数) left join (机器类型:开启机器数) 变成了 (机器类型:总数:具有心跳机器数:开启机器数)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

select a.machineType, a.totalCount, b.machineWithLivingHeartbeatCount, c.machineStatusOnCount
        from (select machine_type machineType, count(*) totalCount from atc_machine group by machine_type) a
                 left join
             (select machine_type, count(*) machineWithLivingHeartbeatCount
              from atc_machine
              where DATE_ADD(last_heartbeat, INTERVAL #{interval} MINUTE) > NOW()
              group by machine_type) b
             on a.machineType = b.machine_type
                 left join
             (select machine_type, count(*) machineStatusOnCount
              from atc_machine
              where status = 1
              group by machine_type) c
             on a.machineType = c.machine_type;

https://database.51cto.com/art/202011/631613.htm