代码之家 › 专栏 › 技术社区 › Ben Blank Jarret Hardie

在sql中选择多个m:n关系的最佳方法是什么?

join sql

Ben Blank Jarret Hardie · 技术社区 · 17 年前

我已经开始了一个项目,它鼓励我扩展我的sql知识。我已经学到了很多,但是我已经到了可以看到问题的地步,但是我还不知道该如何研究解决方案。谷歌和mysql文档我都这么认为,但要么我问错了问题,要么我不知道怎么问对了问题。我的数据由三个主表构成,其中一个表与另外两个表之间有m:n关系。我将这些关系存储在另一对表中,因为我认为这是“最好的方法”:

books (book_id INT PRIMARY KEY, book_title VARCHAR)
authors (author_id INT PRIMARY KEY, author_name VARCHAR)
subjects (subject_id INT PRIMARY KEY, subject_name VARCHAR)
book_authors (book_id INT, author_id INT)
book_subjects (book_id INT, subject_id INT)

(前三个表实际上有两列以上,但它们并不相关。)

编辑:

显然,我不擅长问清楚的问题,但我已经知道了。-)

我试图解决的问题是如何最有效/高效地将数据库中的数据输入到我的应用程序中。一旦我有了它,我可以重新安排它,无论我需要,我相信我可以这样做,无论数据是如何“塑造”出来的数据库。用五个独立的 SELECT * FROM â¦ 声明和我之前发布的跨产品“frankenjoin”之间的关系是微不足道的。然而,我对sql的了解足以发现,前者剥夺了数据库引擎完成其工作的机会。我不要对sql的了解足以说明后者是否同样糟糕。

那么这个呢:与其问解决方案有什么问题我想到了什么你的解决方案?如果你有这样的数据你从数据库中选择它?(出于什么原因?)你能把桌子安排得不同点吗?

6 回复 | 直到 17 年前

tvanfosson 17 年前

我认为您可能希望将其作为两个查询来完成,一个用于作者,一个用于主题,但是如果需要,您可以使用union将它们组合成一个结果。

SELECT books.book_id, books.book_title, 
      'author' as record_type, authors.author_name as record_value
LEFT JOIN book_authors ON books.book_id = book_authors.book_id
LEFT JOIN authors ON authors.item_id = book_authors.author_id
UNION
SELECT books.book_id, books.book_title,
       'subject' as record_type, subjects.subject_name as record_value
LEFT JOIN book_subjects ON books.book_id = book_subjects.book_id
LEFT JOIN subjects ON subjects.subject_id = book_subjects.subject_id;

我不确定它是否真的保存了除了往返数据库之外的任何东西。我提供它只是为了潜在的贡献你的知识,而不是希望它有助于你眼前的问题。

Hank Gay 17 年前

你怎么认为应该回来?元组中不能有从查询返回的列表,因此当前结果似乎是在一个查询中返回所有数据的唯一方法。

更新:sql不支持层次结构,因此您必须自己构建它(假设您没有使用一个orm工具来为您做这件事)。一种方法是去数据库处理所有的书,然后对每本书的作者进行一次访问,对该书的主题进行一次访问。不过,这可能是一个可怕的数据库旅行。另一种方法是将所有数据(如您所问)带回,然后在此基础上构建一个层次结构。哪个更好取决于很多因素。我可能会采用one-db-trip解决方案,尽管客户端代码可能更复杂;一般规则是,往返数据库的费用很高,所以我倾向于采用不会随着结果数线性增加此类往返次数的解决方案。

eaolson 17 年前

在应用程序中创建一些大型的多连接表并对其进行重构,首先会破坏数据库的用途。您的表中的所有内容都很规范,为什么要放弃所有这些工作?

我要做的是建立并维护与数据库的连接。当您需要一些数据时,创建一个select语句,该语句只连接您需要的表,并且只提供您此时需要的数据,然后将其发送到数据库,并处理结果。如果在查询数据库时有人正在插入和更新数据库中的行,则结果将是最新的。

如果你需要艾米莉·狄金森所有的书,那就加入你的图书和作者表吧。如果你需要所有写糕点制作书籍的作者,只需加入authors和subjects表。

Jim Petkus 17 年前

我通常不觉得用这种方式把数据恢复形状有用。我可以相互关联的多个结果集往往更有用。这样您的结果集中也就不会有无关的数据了(8行返回,每行的图书都有相同的列)。

dkretz 17 年前

你得到的正是你所描述的,任何标准的主流开发人员都会以同样的方式完成同样的事情。但显然它与你对形状和大小的心理预期不符。

从三本书开始,有不同数量的作者和主题,列出你想看的东西。然后我们可以讨论怎么去那里。

我想你找不到任何能让你达到目的的方格。也许你想要更像树结构的东西?

+ Title 1
+----+ Authors
     + ---- Author 1
     + ---- Author 2
+    + Topics
     + ---- Topic A
etc.

我发现,在另一个抽象层中,使用映射工具可以最容易地派生出这些行中的任何内容;尽管有一些方法可以扭转关系数据,但如果您具备这些技能(例如,分析多维数据集和查询,返回依赖的二次查询,它们通常涉及专有扩展(例如linq);但在完全掌握此类内容之前,您可能不想使用更复杂的内容(阅读:complex)。

-1

Matthew 17 年前

可以使用ms sql server返回多个结果集。从书本中选择*; 从图书作者中选择*; 从作者中选择*; 从书本主题中选择*; 从受试者中选择*;

然后使用该信息加载具有关系的.NET数据集,或者创建实体对象(如果不使用.NET或数据集,则封装关系)。

如果您使用的是.NET3.5,那么请查看linqtoSQL或linqtoEntities,以简化创建所需代码的工作。