mysql - 如何根据同一个表上的 select 语句的结果创建 View ？

Question

我有一个包含三列的表格。 Material 的 ID、它所属的系列(有某种分组)、测试类型以及该测试的结果(数字 (1-4))。测试类型指向一个包含测试列表的表，结果是一个数字。我正在尝试在适当的情况下创建一个查询/ View ，以便我可以获得一系列 Material 的结果，并且对于每个测试都有一个新列，其中的单元格显示该系列中所有 Material 中该测试的平均值...

我想，由于这是一个小项目，而且我知道测试(不认为它们会改变，但不确定)，所以我可以将测试(它们是其中 6 个)添加为列中的列表而不是将其作为数据，但我觉得不合适。此外，将来可能会添加测试。但与此同时，添加列并不困难，我只需将平均值代码更改为忽略值到特定值，这样我就可以在添加测试之前区分值。

那么我该如何去做呢？我这样做是个好主意吗？

现在我所拥有的可能是为每对系列/测试创建一个 select 语句，然后以某种方式使用这些查询的结果创建一个 View (是否是虚拟表)。

如果表格是

test_result
family_id | material_id | test_id | result

查询将是

SELECT AVG(result) AS 'TEST'
FROM test_result
WHERE family_id = 'family_id'
  AND test_id = 'test_id;

但我不确定如何继续，或者是否有比执行 6 次并以某种方式组合结果更好的方法

Answer 1

我不知道你对数据库设计了解多少。

您所问的问题是，在一个表中包含 6 列 test_id 与在另一张表中以 family_id 和 test_id 作为主键(唯一标识符)是有关数据库设计的基本问题。它与第一范式有关。如果您愿意，您可以研究第一范式和一般数据标准化。

对于本例，这是一个过于简化的版本。

一张表中六列的方法有两个大问题。

第一个问题是:当他们改变主意并添加第七个测试时会发生什么？如果这种情况永远不会发生，一切都会好起来的。但如果没有，您必须通过添加另一列来更改表，并且必须更改引用该表的任何查询。如果这只是您的情况下的一个查询，您可以对其进行管理。如果有数百个查询可能引用该表，并且其中一些查询位于可能需要维护周期来修改查询的应用程序中，那么这可能是一场噩梦。这就是为什么数据库教程充满了 Material ，如果您只做过这个小项目，您可能不需要学习这些 Material 。

第二个问题是:当您必须编写一个查询来查找每次出现的 testid = 4 时，无论该值存储在六列中的哪一列中，会发生什么？您必须在 WHERE 子句中编写一个包含五个 OR 运算符的查询。这是乏味的、容易出错并且运行缓慢的。再说一遍，这可能永远不会成为问题。

通常更好的方法是创建第三个表，其中 family_id 和 test_id 作为列，并且可能将结果作为第三列(我不确定material_id 是什么......有 Material 表吗？)

第一个表“family”包含 family_id 以及仅依赖于该家族的任何数据，例如 family_name。

第二个表测试包含 test_id 和仅依赖于测试的任何数据，例如 test_name。

第三个表包含依赖于两者的数据。

然后，您编写一个 View ，将所有三个表连接在一起，使其看起来像您想要的使用方式。

如果这涵盖了许多您已经知道的概念，我深表歉意。再说一遍，我无法从你的问题中看出。