mysql - 如果它们对同一个人 ID 重复，我需要在不同列中显示行值

Question

现在我有这样的输出，但我需要以不同的方式输出

Peron_id  Diagnosis1 doagnosis2 diagnosis3 diagnosis4 
 1         fever
 2         fever
 2         backpain
 2         UTI

我需要这样的输出

Peron_id  Diagnosis1 doagnosis2 diagnosis3 diagnosis4 
 1         fever
 2         fever     backpain    UTI

请帮助我。

我现在构建了完整的查询并且它显示了一些错误（操作数应该包含 1 列。我将查询粘贴在下面。请帮助我

SELECT
    visits.id as VisitId,
    visits.person_id as MemberId,
    dept.name as Specialty,
    visits.date as VisitDate,
    visits.followup_to_visit_id as FollowToVisitID,
    visits.chief_complaint as ChiefComplaint1,
    visits.chief_complaint_2 as ChiefComplaint2,
    visits.chief_complaint_3 as ChiefComplaint3,
    visits.valid_state as ValidStatus,
    visits.clinic_type as ClinicType,
    visits.illness_duration as IllnessDuration,
    vitals.temperature_f as Temp,
    vitals.pulse as Pulse,
    vitals.respiratory_rate as RespRate,
    vitals.bp_systolic as BPSystolic,
    vitals.bp_diastolic as BPDiastolic,
    vitals.height_cm as HeightCms,
    vitals.weight_kg as WeightKgs,
    vitals.waist_cm as WaistCms,
    vitals.hip_cm as HipCms,
    referral.referred_to_provider as ReferredOut,
    visits.provider_id as DoctorId,
    prov.full_name as DoctorName,
    prov.registration_number as DoctorRegNum,
    prov.qualification as DoctorQual,
    provloc.name as VisitUphc,
    tk.name as VisitUlb,
    districts.name as VisitDistrict,
    "TN" as VisitState,
    provloc.cachment_code as VisitCatchment,
    visits.entry_date as VisitEntryDate,
    (visits.end_time - visits.begin_time) as VisitEntryTimeTaken,
    visits.created_date as VisitCreateDt,
    visits.last_modified_date as VisitLastModDt,
(Select visit_id,
max(case when i=1 then diagnosis end)as provisional_diagnosis_1,
max(case when i=2 then diagnosis end)as provisional_diagnosis_2,
max(case when i=3 then diagnosis end)as provisional_diagnosis_3,
max(case when i=4 then diagnosis end)as provisional_diagnosis_4
from
(   
select visit_id, diagnosis as diagnosis,
        @i :=IF(@iter=visit_id,@i+1,1)as i,
        @iter :=visit_id
    from  visit_diagnosis_entries v1 
    join (select @iter :=0) v2
      ) vv)
FROM 
visits
    LEFT JOIN visit_vitals vitals ON vitals.visit_id = visits.id 
    LEFT JOIN visit_referral_entries referral ON referral.visit_id = visits.id
    LEFT JOIN visit_diagnosis_entries diag ON diag.visit_id = visits.id 
    INNER JOIN providers prov ON prov.id = visits.provider_id 
    INNER JOIN provider_locations provloc ON provloc.id = visits.provider_location_id 
    INNER JOIN talukas tk ON tk.id = provloc.taluka_id  
    INNER JOIN districts ON districts.id = provloc.district_id 
    INNER JOIN departments dept ON dept.id = visits.department_id limit 5; 

以前我用一张表解释过，现在我需要所有的列，另外还需要这些列。

标签： mysqlsql

Answer 1

如果您使用的是 MySQL 8+，那么我们可以在以下帮助下尝试枢轴查询ROW_NUMBER：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Peron_id ORDER BY Diagnosis) rn
    FROM yourTable
)

SELECT
    Peron_id,
    MAX(CASE WHEN rn = 1 THEN Diagnosis END) AS Diagnosis1,
    MAX(CASE WHEN rn = 2 THEN Diagnosis END) AS Diagnosis2,
    MAX(CASE WHEN rn = 3 THEN Diagnosis END) AS Diagnosis3,
    MAX(CASE WHEN rn = 4 THEN Diagnosis END) AS Diagnosis4
FROM cte
GROUP BY
    Peron_id;