POSTGRES -将同一列与一行中的不同文本组合在一起

Question

我有三张桌子要合并。我有个餐桌医生，病人和手术。医生代表所有的医生。病人代表病人，手术控制病人和医生的primaryKeys。如何编写一个查询，向我展示chiefDoctor和assistantDocotor以及耐心？到目前为止我达到了什么？

从酋长或助理医生中显示名字和姓氏。我该怎么写才能展示一张有主任和助理的桌子？

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

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

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)

但我觉得这不是你关心的主要问题，为了缩短解决方案，我忽略了这一点。