问题

这是你要的翻译：

这里有三个表：

Table1: MedicineForPrescription

|Id|MedicineID|prescriptionId|
|--|----------|--------------|
|12|14|14481|	       
|13|15|14481|
|14|16|14481|

Table2: Prescription

|Id     | PatientId|
|-------|----------|
|14481 |  1|

Table3: Patient

|Id  | FirstName  | LastName|
|----|------------|---------|
|1   | Sam        | Suan|

我想要展示如下：

|FirstName |LastName |PrescriptionId| Medicine1 | Medicine2| Medicine3 |
|----------|---------|--------------|-----------|----------|-----------|
|sam|suan|14481|14|15|16|

请问是否有人可以提供我SQL Server查询？

答案1

得分: 2

对于每位患者处方中固定数量的药物，您可以联接表格，然后进行条件聚合透视：

select pa.firstName, pa.lastName, me.prescriptionId,
    max(case when me.rn = 1 then medecineId end) as medecine1,
    max(case when me.rn = 2 then medecineId end) as medecine2,
    max(case when me.rn = 3 then medecineId end) as medecine3
from patient pa
inner join prescription pr on pr.patientId = pa.id
inner join (
    select me.*, row_number() over(partition by prescriptionId order by id) rn
    from medecineForPrescription me
) me on me.prescriptionId = pr.id
group by pa.id, pa.firstName, pa.lastName, me.prescriptionId

select pa.firstName, pa.lastName, me.prescriptionId,
    max(case when me.rn = 1 then medecineId end) as medecine1,
    max(case when me.rn = 2 then medecineId end) as medecine2,
    max(case when me.rn = 3 then medecineId end) as medecine3
from patient pa
inner join prescription pr on pr.patientId = pa.id
inner join (
    select me.*, row_number() over(partition by prescriptionId order by id) rn
    from medecineForPrescription me
) me on me.prescriptionId = pr.id
group by pa.id, pa.firstName, pa.lastName, me.prescriptionId