一对多关系的 SQL 视图、性能和计数

Question

我需要一些帮助来为我的一堆表形成基本的 SQL-VIEW。这是一个快速概述，

• 我有一个 ClaimDetail 表，它有一些查找字段，如 StatusID、BrandID、SalespersonID 等。
• 像往常一样，查找字段映射到主表，如 MasterStatus、MasterBrand、... {Structure: ID, Title}
• 还有另外两个表“注释”和“文件”。一个声明可以有多个评论和多个文件。
• 我需要显示一个仪表板，其中包含索赔列表。我需要显示主表中的标题以及评论和评论的数量。文件。

现在，我对该仪表板有两种视图，一种是针对“客户”类型的用户，仅限于某些详细信息，另一种是针对内部用户的详细视图。您可以说客户视图是内部视图的子集。

我看到两个选择 -

1. 选项#1：创建单个 vw_Internal 视图并 用它来获取数据 用户。
2. 选项#2：我创建一个 vw_Customer 其中只有那些字段 客户需要并且 然后我创建一个 vw_Internal 将类似于：vw_Customer INNER JOIN 主表。简而言之我会延长 要包含的基本 vw_Customer 更多领域。

从速度和性能的角度来看，选项#2 有意义吗？ Opt#1 很简单，但考虑到大量记录，我想确保客户不必为那些不会包含在仪表板中的额外查找等待更长的时间。

最后，我提到的最后一个功能有办法实现吗？即获取与ClaimDetail 表具有一对多关系的评论和文件的计数。我只需要计数或至少一个布尔字段，该字段表示声明是否有任何注释（文件相同） - 如果计数 = 0，则为 false。我还担心此功能对性能的影响。

提前致谢。

Answer 1

关于视图定义，我将构建两个视图，并将它们分开——两个视图都不会引用另一个视图。这将允许您独立地优化查询，并且避免您在视图之上分层视图时遇到的任何问题；太多的层会使数据库管理、维护和重构变得特别具有挑战性。

对于数据聚合，常见的策略包括以下几种。比较、对比、测试和推断，看看什么最适合您的环境：

子查询

SELECT mt.Id, st1.HowMany, st2.HowManyOther, <etc>
 from MainTable mt
  inner join (select Id, count(*) HowMany
               from SubTable1
               group by Id) st1
   on st1.Id = mt.Id
  inner join (select Id, count(*) HowMany
               from SubTable2
               group by Id) st2
   on st2.Id = mt.Id

相当简单，尽管子查询可能会变得有点昂贵，即使有适当的索引。

count(distinct xx)

SELECT mt.Id, count(distinct st1.UniqueKey) HowMany, count(distinct st2.UniqueKey) HowManyOther, <etc>
 from MainTable mt
  inner join SubTable1 st1
   on st1.Id = mt.Id
  inner join SubTable2
   on st2.Id = mt.Id

这需要“子表”中有一个唯一的列，如果必须处理外连接或 NULL，就会变得混乱。

---

添加

---

首先，在上述任一查询中用（左）外连接替换内连接将从子表中产生 0+ 计数，只要您确保计数是在“右”表上完成的（因为 NULL 不会）没有得到统计）。要确定哪一个最适合您的环境，您必须编写并测试这两个查询。我猜第二个，因为第一个需要对子查询的表进行表扫描，而第二个执行连接，因此可能优化得更好，但 SQL 查询优化器比我更聪明（因为它知道你的索引并具有分布直方图你的数据）所以你想看看它会产生什么结果。

关于“分层视图”，如果我遵循正确的逻辑，我建议将内部视图构建为复杂/综合查询（所有联接、所有相关列），然后构建希望的客户视图就像

SELECT <customerOnlyColumns>
 from vw_Internal