下面上一段SQL:
SELECT
t.company_id AS companyId,
t.company_name AS companyName,
COUNT( DISTINCT os.id ) AS stationCount,
COUNT( DISTINCT opsdr.device_id ) AS deviceCount,
COUNT( opd.id ) AS planCount,
COUNT( opd.`status` = '0' OR NULL ) AS oneCount,
COUNT( opd.`status` = '1' OR NULL ) AS twoCount,
COUNT( opd.`status` = '2' OR NULL ) AS threeCount,
COUNT( opd.`status` = '3' OR NULL ) AS fourCount
FROM
( SELECT op.company_id, op.company_name FROM op_program op WHERE op.envir_code = 'noise' GROUP BY company_id ) t
LEFT JOIN op_program op ON op.company_id = t.company_id
LEFT JOIN op_program_station_ref opsr ON opsr.program_id = op.id
LEFT JOIN op_station os ON opsr.station_id = os.id
LEFT JOIN op_program_station_device_ref opsdr ON opsr.id = opsdr.program_station_id
LEFT JOIN op_plan_details opd ON opsdr.id = opd.program_station_device_id
AND opd.check_time >= '2020-01-01'
GROUP BY
t.company_id
里面的count不带OR NULL时,为统计这个字段的所有数据,此时想要统计该字段不同条件下的数据,就需要加上OR NULL才可以生效,原因:
count里面传递参数计算统计时根本不关心条件表达式里面的返回值,无论传什么都会进行统计,而只有在值为null时才会不统计,此时加上OR NULL会使在OR前面的条件为false时,值为null,进而使mysql跳过这条数据不统计