sql - 枢轴然后按价值分组

Question

我有一个logs具有以下定义的表：

   Column         |         Type          | Collation | Nullable | Default
------------------+-----------------------+-----------+----------+---------
 id               | integer               |           | not null |
 work_location_id | uuid                  |           | not null |
 hard_disk_id     | integer               |           | not null |

以及works具有以下定义的表：

   Column    |         Type          | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
 id          | integer               |           | not null |
 location_id | uuid                  |           | not null |
 f_index     | integer               |           | not null |
 f_name      | character varying(40) |           | not null |
 f_value     | character varying(40) |           | not null |

该logs表包含以下数据：

 id |          work_location_id            | hard_disk_id 
----+--------------------------------------+--------------
  1 | 40e6215d-b5c6-4896-987c-f30f3678f608 | 1
  2 | 3f333df6-90a4-4fda-8dd3-9485d27cee36 | 2
  3 | c17bed94-3a9c-4c21-be49-dc77f96d49dc | 3
  4 | 6ecd8c99-4036-403d-bf84-cf8400f67836 | 4
  5 | 6ecd8c99-4036-403d-bf84-cf8400f67836 | 5

该works表具有以下数据：

 id |             location_id              | f_index |   f_name    |  f_value
----+--------------------------------------+---------+-------------+------------
  1 | 40e6215d-b5c6-4896-987c-f30f3678f608 |       1 | plot_crop   | pears
  2 | 3f333df6-90a4-4fda-8dd3-9485d27cee36 |       1 | plot_crop   | pears
  3 | c17bed94-3a9c-4c21-be49-dc77f96d49dc |       1 | plot_crop   | pears
  4 | 1cdc7c05-0acd-46cb-b48a-4d3e240a4548 |       1 | plot_crop   | pears
  5 | dae1eee7-508f-4a76-8906-8ff7b8bfab26 |       1 | plot_crop   | pears
  6 | 6ecd8c99-4036-403d-bf84-cf8400f67836 |       1 | plot_id     | 137
  7 | 6ecd8c99-4036-403d-bf84-cf8400f67836 |       2 | farmer_name | John Smith

期望的输出

我希望能够查询这两个表并获得以下输出

              location_id              | plot_id | farmer_name
---------------------------------------+---------+-------------
  40e6215d-b5c6-4896-987c-f30f3678f608 | None    | None
  3f333df6-90a4-4fda-8dd3-9485d27cee36 | None    | None
  c17bed94-3a9c-4c21-be49-dc77f96d49dc | None    | None
  6ecd8c99-4036-403d-bf84-cf8400f67836 | 137     | John Smith

注意 for location_id = 6ecd8c99-4036-403d-bf84-cf8400f67836，两个值现在都显示在一行中。我尝试使用group by location_id但没有用，我仍然得到重复。

我还创建了一个db-fiddle。

标签： sqlpostgresql

Answer 1

这看起来像条件​​聚合：

select location_id,
       max(f_value) filter (where f_name = 'plot_id') as plot_id,
       max(f_value) filter (where f_name = 'farmer_name') as farmer_name
from t
group by location_id;

在其他数据库中，您只需使用：

max(case when f_name = 'plot_id' then f_value end) as plot_id