将os1-os6和ch1-ch6值放入诊断表中。 JOIN 以及 GROUP BY :

SELECT COUNT(*), d.diagnosis
FROM patients p
RIGHT JOIN diagn d
   on p.diagnosis like concat('%', d.diagnosis, '%')
group by d.diagnosis

不确定这是否有效。

SELECT COUNT(*) 
FROM patients 
WHERE REGEXP_LIKE(diagnosis, "(OS[1-6]|CH[1-6])")

如果您感兴趣，也可以查看下面的链接。但不确定这是不是你想要的：
https://dev.mysql.com/doc/refman/8.0/en/regexp.html

您也可以尝试：

SELECT COUNT(*), diagnosis
FROM patients
GROUP BY diagnosis;

SELECT sum(OS1) as OS1_total, sum(OS2) as OS2_total, sum(OS3) as OS3_total,
sum(OS4) as OS4_total, sum(OS5) as OS5_total, sum(OS6) as OS6_total, 
sum(CH1) as CH1_total, sum(CH2) as CH2_total, sum(CH3) as CH3_total,
sum(CH4) as CH4_total, sum(CH5) as CH5_total, sum(CH6) as CH6_total
FROM
(
SELECT (case when diagnosis like '%OS1%' then 1 else 0 end) as OS1,
(case when diagnosis like '%OS2%' then 1 else 0 end) as OS2,
(case when diagnosis like '%OS3%' then 1 else 0 end) as OS3,
(case when diagnosis like '%OS4%' then 1 else 0 end) as OS4,
(case when diagnosis like '%OS5%' then 1 else 0 end) as OS5,
(case when diagnosis like '%OS6%' then 1 else 0 end) as OS6,
(case when diagnosis like '%CH1%' then 1 else 0 end) as CH1,
(case when diagnosis like '%CH2%' then 1 else 0 end) as CH2,
(case when diagnosis like '%CH3%' then 1 else 0 end) as CH3,
(case when diagnosis like '%CH4%' then 1 else 0 end) as CH4,
(case when diagnosis like '%CH5%' then 1 else 0 end) as CH5,
(case when diagnosis like '%CH6%' then 1 else 0 end) as CH6
FROM patients
) as mytable