MySQL嵌套SELECT查询：基于指定患者ID统计疾病类型

Alright, let's build on your existing query to get the diagnostic stats you need. You've already nailed the first part: fetching the 205 patient IDs from active visits in March 2018. Now we'll pair that list with your diagnoses table to count the relevant conditions per patient.

Assumptions #

I'm assuming you have a diagnoses table with at least these columns:

• patient_id: Links back to the visit table
• disease_category (or similar): A field that categorizes diseases into groups like "Cardiovascular" or "Respiratory" (if you use specific disease names instead, I'll note how to adjust later)

Option 1: Embed Your Patient List as a Subquery #

This is a straightforward approach that wraps your existing ID query into the main stats query:

SELECT 
  patient_list.patient_id,
  -- Count cardiovascular diseases
  COUNT(CASE WHEN diagnoses.disease_category = 'Cardiovascular' THEN 1 END) AS cardiovascular_count,
  -- Count respiratory diseases
  COUNT(CASE WHEN diagnoses.disease_category = 'Respiratory' THEN 1 END) AS respiratory_count
FROM (
  -- Your original patient ID query
  SELECT patient_id 
  FROM visit 
  WHERE MONTH(date_of_visit) = 3 
    AND YEAR(date_of_visit) = 2018 
    AND visit_status = 'Active' 
  GROUP BY patient_id
) AS patient_list
-- Use LEFT JOIN to include patients with no matching diagnoses (counts will show as 0)
LEFT JOIN diagnoses ON patient_list.patient_id = diagnoses.patient_id
GROUP BY patient_list.patient_id
ORDER BY patient_list.patient_id;

Option 2: Use a CTE for Better Readability #

If you want cleaner code (especially if you might reuse the eligible patient list later), a Common Table Expression (CTE) is perfect:

-- First, define the list of eligible patients
WITH eligible_patients AS (
  SELECT patient_id 
  FROM visit 
  WHERE MONTH(date_of_visit) = 3 
    AND YEAR(date_of_visit) = 2018 
    AND visit_status = 'Active' 
  GROUP BY patient_id
)
-- Now calculate disease counts
SELECT 
  ep.patient_id,
  COUNT(CASE WHEN d.disease_category = 'Cardiovascular' THEN 1 END) AS cardiovascular_count,
  COUNT(CASE WHEN d.disease_category = 'Respiratory' THEN 1 END) AS respiratory_count
FROM eligible_patients ep
LEFT JOIN diagnoses d ON ep.patient_id = d.patient_id
GROUP BY ep.patient_id
ORDER BY ep.patient_id;

Quick Adjustments for Your Schema #

• If your diagnoses table uses specific disease names instead of categories (e.g., "Hypertension", "Asthma"), update the CASE statements to match those values:

  COUNT(CASE WHEN d.disease_name IN ('Hypertension', 'Heart Failure', 'Stroke') THEN 1 END) AS cardiovascular_count,
  COUNT(CASE WHEN d.disease_name IN ('Asthma', 'COPD', 'Pneumonia') THEN 1 END) AS respiratory_count
  

• If you want a total count across all 205 patients (instead of per-patient stats), remove the GROUP BY patient_id and adjust the select to sum the cases:

  SELECT 
    COUNT(CASE WHEN d.disease_category = 'Cardiovascular' THEN 1 END) AS total_cardiovascular_cases,
    COUNT(CASE WHEN d.disease_category = 'Respiratory' THEN 1 END) AS total_respiratory_cases
  FROM eligible_patients ep
  LEFT JOIN diagnoses d ON ep.patient_id = d.patient_id;
  

内容的提问来源于stack exchange，提问作者alim1990