sql - POSTGRES - 将同一列与不同文本组合成一行

Question

我要合并三个表。我有一个表医生，病人和手术。 Doctor代表了所有医生的精神。 Patient 代表一个病人，操作持有来自病人和医生的主键。我如何编写一个查询来显示 chiefDoctor 和 assistantDocotor 以及 Patient？到目前为止我得到了什么？

显示首席医生或助理医生的名字和姓氏。我要如何写才能显示一张既有主任又有助理的表格？

select a.vorname|| ' ' || a.nachname AS leitenderArzt, p.firstname || ' ' || p.lastname AS patient
from angestellter a inner join operation o on a.id = o.leitenderarzt
inner join patient p on o.patientident=p.ident

| id | firstname       | lastname      | patient      |
| 1  | ImA             | ChiefDoctor1  | p.firstname  |
| 3  | ImA             | ChiefDoctor3  | p.firstname  |

我的数据库的底层结构和表示。

CREATE TABLE doctor
(
  id serial NOT NULL,  
  firstname character varying(255) NOT NULL,
  lastname character varying(255) NOT NULL,  
  CONSTRAINT angestellter_pkey PRIMARY KEY (id),
}

 Table doctor
 |id | firstname | lastname        |
 | 1 | ImA       | ChiefDoctor1    |
 | 2 | ImA       | AssistantDoctor |
 | 3 | ImA       | ChiefDoctor2    |

CREATE TABLE patient
(
  ident serial NOT NULL,  
  firstname character varying(255) NOT NULL,
  lastname character varying(255) NOT NULL,
  CONSTRAINT patient_pkey PRIMARY KEY (ident),
}

Table patient
| ident | firstname | lastname |
| 1     | Operated  | ME       |

CREATE TABLE operation
(
  id serial NOT NULL,
  chiefDoctor integer NOT NULL,
  AssistantDoctor integer NOT NULL,
  patientident integer NOT NULL,
  CONSTRAINT operation_pkey PRIMARY KEY (id),
  CONSTRAINT fkoperation539608 FOREIGN KEY (patientident)
      REFERENCES patient (ident) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkoperation745809 FOREIGN KEY (assistantDoctor)
      REFERENCES angestellter (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkoperation949671 FOREIGN KEY (chiefDoctor)
      REFERENCES angestellter (id) MATCH SIMPLE
}

Table operation
| id | doctorID | doctorID | patientID |
| 1  |    1     |    2     |     1     |

我如何编写查询以显示谁操作了全名患者？它应该看起来像这样。

| id | chiefdoctor       | assistantdoctor      | patient      |
|----| ImA + ChiefDoctor |ImA + AssistantDoctor | Operated + ME|

Answer 1

第一个也是最直观的方法是两次加入 doctor:

select o.id
     , d1.firstname || ' + ' || d1.lastname as chiefdoctor
     , d2.firstname || ' + ' || d2.lastname as assistantdoctor
     , p.firstname || ' + ' || p.lastname as patient
from operation o
join doctor d1
    on o.chiefDoctor = d1.id
join doctor d2
    on o.AssistantDoctor = d2.id
join patient p
    on o.patientident = p.ident

您可能希望像这样修剪名称:

trim(both from d1.firstname) || ' + ' || trim(both from d1.lastname)

但我觉得这不是您主要关心的问题，所以为了缩短解决方案，我将其省略