mysql 怎么查询两个值的where条件不同

Question

我想要得到的 B 值是 ROUND( sum( DEFNUM ) / COUNT( DISTINCT CHASSISNUMBER ), 2 )，其中
COUNT( DISTINCT CHASSISNUMBER )的where 条件是以下条件

 carModel='XXX'  and DATE_SUB(CURDATE(), INTERVAL 117 DAY) <= date(riqi) group by riqi ORDER BY riqi DESC

和 where 条件是以下条件的 sum( DEFNUM )

carModel='XXX' and 专业= 'PP'  and  defectRank='B' and DATE_SUB(CURDATE(), INTERVAL 117 DAY) <= date(riqi) group by riqi ORDER BY riqi DESC

我按照下面的代码查询的结果， CHASSISNUMBER 也经过 专业= 'PP' 和 defectRank='B' 筛选

SELECT riqi as date, ROUND( sum( DEFNUM  ) / COUNT( DISTINCT CHASSISNUMBER ), 2 ) AS B, 基础值,挑战值 FROM 表名 WHERE  carModel='XXX' and 专业= 'PP'  and  defectRank='B' and DATE_SUB(CURDATE(), INTERVAL 117 DAY) <= date(riqi) group by riqi ORDER BY riqi DESC

求问应该怎么写能得到我想要的 B 值
PS：表结构如下图所示

Answer 1

我用了个土办法，先分别查询sum( DEFNUM )和CHASSISNUMBER ，再通过left join和关联字段进行表连接