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

我有三张桌子要合并。我有医生，病人和手术室。医生代表所有的医生。病人代表病人，手术持有病人和医生的主键。
我该如何写一个查询，向我展示酋长、助理和病人？到目前为止我达到了什么程度？
显示一个酋长或助理的姓和名。我该怎么写才能显示一张桌子，里面有主任和助理？

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|

最佳答案

第一个也是最直观的方法是加入医生两次：

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)

但我觉得这不是你最关心的问题，所以为了缩短解决方案，我把它忘了