如何选择具有许多关系作为列名

Question

我有 2 张表。

1. 电影
2. movie_meta

电影表

| id | name         |
---------------------
| 1 | Harry Potter  |
| 2 | Joker         |
| 3 | Need For Speed|

movie_meta表

| id | movie_id | key     | value            |
---------------------------------------------
| 1  | 1        | country | USA              |
| 2  | 1        | genre   | Horror & Sci-Fi  |
| 3  | 1        | actor   | Daniel Radcliffe |

我想实现的是这样的。

| id | name         | country | genre           | actor            |
--------------------------------------------------------------------
| 1  | Harry Potter | USA     | Horror & Sci-Fi | Daniel Radcliffe |

movie_meta表的行数不受限制。那么，如何选择呢？ 谢谢

Answer 1

您可以加入movie_meta三次：

select 
m.*,
c.value country,
g.value genre,
a.value actor
from movie m
inner join movie_meta c on c.movie_id = m.id and c.key = 'country'
inner join movie_meta g on g.movie_id = m.id and g.key = 'genre'
inner join movie_meta a on a.movie_id = m.id and a.key = 'actor'

为了提高性能，您需要在movie_meta(movie_id, key)上有一个索引。

如果元表可能缺少其中一个属性，则可以将inner joins 转换为left joins。

或者你可以做条件聚合(这更整洁，但效率可能较低(：

select 
m.id,
m.name,
max(case when x.key = 'country' then value end) country,
max(case when x.key = 'genre' then value end) genre,
max(case when x.key = 'actor' then value end) actor
from movie m
inner join movie_meta x on x.movie_id = m.id
group by m.id, m.name

Answer 2

一种方法使用条件聚合。 另一个使用多个join。 对于此示例，两者在性能方面可能相似：

select m.*, mmc.value as country, mmg.value as genre, mma.value as actor
from movies m left join
movie_meta mmc
on mmc.movie_id = m.id = and mmc.key = 'country' left join
movie_meta mma
on mma.movie_id = m.id = and mma.key = 'actor' left join
movie_meta mmg
on mmg.movie_id = m.id = and mmg.key = 'genre' ;

请注意，您的示例每部电影只有一个演员和流派。 在现实世界中，这两者都可能具有多个值。