如何从另一个表中计数数据

Question

我有病人表，病人名称和有病人，保险的insurance_cover表。

我需要显示有保险和没有保险的病人，病人人数和保险总数，如果不使用联接，我该怎么做呢？

SELECT 'WITH INSURANCE' AS '',PATIENTNUM, PATIENTSURNAME
from PATIENT where PATIENTNUM 
IN(select PATIENTNUM from INSURANCE_COVER) 

UNION

SELECT 'WITHOUT INSURANCE' AS '',PATIENTNUM, PATIENTSURNAME
from PATIENT where PATIENTNUM 
NOT IN( select PATIENTNUM from INSURANCE_COVER)

这段代码显示了有保险和没有保险的数据。但我不知道如何计算有保险的病人的保险。

我用的不对吗？你能帮帮我吗？

Answer 1

SELECT 
    'WITH INSURANCE' AS '',
    PATIENTNUM, 
    PATIENTSURNAME,
    (SELECT COUNT(*) FROM PATIENT tmp WHERE PATIENTNUM IN(select PATIENTNUM from INSURANCE_COVER) AND tmp.PATIENTNUM = p.PATIENTNUM) AS INSURANCES
FROM
    PATIENT p
WHERE 
    PATIENTNUM IN(select PATIENTNUM from INSURANCE_COVER) 

UNION

SELECT 
    'WITHOUT INSURANCE' AS '',
    PATIENTNUM, 
    PATIENTSURNAME,
    0 AS INSURANCES
FROM
    PATIENT 
WHERE
    PATIENTNUM NOT IN( select PATIENTNUM from INSURANCE_COVER)