时间戳

首页 > 解决方案 > Postregsql 根据列值将检索到的一条记录转换为两条记录

sql - Postregsql 根据列值将检索到的一条记录转换为两条记录

问题描述

我有两个 Postgresql 表：

Searches:
+----+------------+-----------+-----------+
| id | patient_id | status_a  | status_b  |
+----+------------+-----------+-----------+
| 1  | 1          | Added     | Added     |
+----+------------+-----------+-----------+
| 2  | 2          | Added     | NULL      |
+----+------------+-----------+-----------+

Patients:
+----+------+---------+
| id | name | country |
+----+------+---------+
| 1  | John | England |
+----+------+---------+
| 2  | Tim  | France  |
+----+------+---------+

我想检索“所有将status_aORstatus_b设置为‘已添加’的患者，根据状态列的值创建不同的记录”。

我需要能够做到这一点：

+------------+------+---------+-----------+-----------+
| patient_id | name | country | status_a  | status_b  |
+------------+------+---------+-----------+-----------+
| 1          | John | England | Added     | NULL      |
+------------+------+---------+-----------+-----------+
| 1          | John | England | Null      | Added     |
+------------+------+---------+-----------+-----------+
| 2          | Tim  | France  | Added     | NULL      |
+------------+------+---------+-----------+-----------+

或者，甚至更好：

+------------+------+---------+-----------+
| patient_id | name | country | status    |
+------------+------+---------+-----------+
| 1          | John | England | A         |
+------------+------+---------+-----------+
| 1          | John | England | B         |
+------------+------+---------+-----------+
| 2          | Tim  | France  | A         |
+------------+------+---------+-----------+

有什么解决办法吗？

标签： sqlpostgresqlselect

解决方案

您需要searches使用 将表格转为行union。

select p.id as patient_id, p.name, p.country, s.status
  from patients p
       join (select patient_id, 'A' as status
               from searches
              where status_a is not null
             union
             select patient_id, 'B' as status
               from searches
              where status_b is not null) as s
         on s.patient_id = p.id
 order by p.id, s.status;

推荐阅读