时间戳

首页 > 解决方案 > 在连接表上存储实体列表

sql - 在连接表上存储实体列表

问题描述

背景

本质上，我想存储类似的东西：

{
  id : 1, 
  name : 'john', 
  favorite_pets : ['cat', 'dog'], 
  favorite_colors : ['red', 'white', 'green']
}

在具有三个表的关系数据库中：

主表：

id name
1  John

表fav_pets：

id pet
1  cat
1  dog

表fav_colors：

id pet
1  red
1  white
1  green

问题

我想要一个返回这个的 SQL 查询：

id  name  pet  color
1   john  cat  red
1   john  dog  white
1   john  null green

问题是如果我这样做：

select * from main 
outer join fav_pets on main.id=pet.id 
outer join fav_colors on main.id=fav_colors.id;

它将返回一个包含 6 行的结果集：

id  name  pet  color
1   john  cat  red
1   john  cat  white
1   john  cat  green
1   john  dog  red
1   john  dog  white
1   john  dog  green

我能否在一个 SQL 查询中不重复行来完成对象的加载？

标签： sqlarraysobjectrelational-database

解决方案

您目前正在加入与主键不同的实体。您需要加入外键。如果 John 可以拥有最喜欢的宠物和最喜欢的颜色，那么主表应该有用于 和 的列，这些列是和fav_pet_id的fav_color_id外键。您还需要将 fav_colors 中的列名从 更改为。一旦这些更改到位，您的查询应如下所示：fav_pet.idfav_color.idpetcolor

select main.name, fav_pets.pet, fav_colors.color 
from main 
left outer join fav_pets on main.pet_id=pet.id 
left outer join fav_colors on main.color_id=fav_colors.id;

阅读表规范化、主键和外键。从这个链接开始https://www.w3schools.com/sql/sql_create_db.asp。

推荐阅读