如何编写SQL查询合并表与聚合诊断名称以消除患者数据冗余

Let’s walk through two practical, actionable approaches to eliminate unnecessary data duplication in your SQL workflows:

1. Merge Tables with Subqueries/JOINs to Reduce Redundancy #

If you’re dealing with data spread across multiple tables (like a Patient table and a Diagnosis table where patient details are repeated in both), we can use subqueries or JOIN operations to pull together only the necessary data without storing duplicates.

Let’s use a common scenario for context:

• Patient table has columns: PatientID, PatientName, Age, Address
• Diagnosis table has columns: DiagnosisID, PatientID, DiagnosisName, DiagnosisDate

Instead of storing PatientName/Age/Address in both tables, we link them via PatientID to get a complete view. Here are two ways to do this:

Using JOIN (Recommended for Readability & Performance) #

SELECT 
    p.PatientID,
    p.PatientName,
    p.Age,
    d.DiagnosisName,
    d.DiagnosisDate
FROM Patient p
INNER JOIN Diagnosis d ON p.PatientID = d.PatientID
-- Add a WHERE clause if you need to filter for specific patients
-- WHERE p.PatientID = 456;

Using Subquery #

SELECT 
    PatientID,
    (SELECT PatientName FROM Patient WHERE PatientID = d.PatientID) AS PatientName,
    (SELECT Age FROM Patient WHERE PatientID = d.PatientID) AS Age,
    DiagnosisName,
    DiagnosisDate
FROM Diagnosis d;

The core idea here: We only store critical patient data once in the Patient table, and reference it via PatientID in the Diagnosis table. No more repeating patient details across every diagnostic record!

2. Aggregate Diagnosis Names to Avoid Redundancy in Patient Records #

If your patient table is cluttered because each diagnosis creates a new row for the same patient, we can aggregate multiple DiagnosisName entries into a single, formatted string per patient. This way, each patient appears only once in the result set, with all their diagnoses grouped together.

The exact function depends on your database system:

For MySQL/MariaDB: Use GROUP_CONCAT #

SELECT 
    p.PatientID,
    p.PatientName,
    GROUP_CONCAT(d.DiagnosisName SEPARATOR ', ') AS CombinedDiagnoses
FROM Patient p
INNER JOIN Diagnosis d ON p.PatientID = d.PatientID
GROUP BY p.PatientID, p.PatientName;

For SQL Server: Use STRING_AGG #

SELECT 
    p.PatientID,
    p.PatientName,
    STRING_AGG(d.DiagnosisName, ', ') AS CombinedDiagnoses
FROM Patient p
INNER JOIN Diagnosis d ON p.PatientID = d.PatientID
GROUP BY p.PatientID, p.PatientName;

For PostgreSQL: Use STRING_AGG #

SELECT 
    p.PatientID,
    p.PatientName,
    STRING_AGG(d.DiagnosisName, ', ') AS CombinedDiagnoses
FROM Patient p
INNER JOIN Diagnosis d ON p.PatientID = d.PatientID
GROUP BY p.PatientID, p.PatientName;

This will return clean, non-redundant results like:

Now your patient data isn’t duplicated across rows for each diagnosis—each patient has one entry, with all their diagnoses neatly aggregated.

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