为什么查询输出中每个实体有多个条目?

发布于 2025-01-11 06:11:18 字数 1005 浏览 0 评论 0原文

想知道为什么我的查询在输出中显示每个实体的多个条目。 据我了解,每个实体只有一项有效政策。

使用 SQL Server Management Studio 创建查询,正确显示的输出具有参数,并且我已在查询中尝试了以下操作。

目前我的 SQL SSMS 查询输出显示以下内容:

Entity_Number  Building_Name PolicyID  Description         Start_Date   End_Date
 400           Xpress         4         5 Day Grace        7/1/2019     9/27/2019
 400           Xpress         18        2 Day Grace        7/3/2018     7/13/2018
 400           Xpress         19        4 Day Grace        2/27/2019    2/27/2019

我真正想知道的是如何深入并找出查询返回倍数的原因?

[询问]

SELECT
e.Entity_Number,
bld.Building_Name,
cbp.PolicyId, 
cbp.Description,  
cbp.StartDate, 
cbp.EndDate

FROM            
dbo.buildings AS bld
INNER JOIN dbo.entities AS e
ON bld.Entity_ID = e.Entity_ID 
INNER JOIN Collections.Building AS cbp
ON bld.Building_ID = cb.BuildingId
INNER JOIN Collections.BuildingProfile AS cbpro
ON cbp.BuildingPolicyId = cbpro.BuildingPolicyId

WHERE
bld.Building_Active = 1
AND e.Active = 1

Would like to know why my query displays multiple entries per entity in the output.
From what I understand there is only one active policy per entity.

Created query with SQL Server Management Studio, my output to display correctly has parameters, and I have tried the following with my query.

Currently my SQL SSMS query output displays the following:

Entity_Number  Building_Name PolicyID  Description         Start_Date   End_Date
 400           Xpress         4         5 Day Grace        7/1/2019     9/27/2019
 400           Xpress         18        2 Day Grace        7/3/2018     7/13/2018
 400           Xpress         19        4 Day Grace        2/27/2019    2/27/2019

What I really would like to know is how do I drill down and find out why my query returns multiples?

[Query]

SELECT
e.Entity_Number,
bld.Building_Name,
cbp.PolicyId, 
cbp.Description,  
cbp.StartDate, 
cbp.EndDate

FROM            
dbo.buildings AS bld
INNER JOIN dbo.entities AS e
ON bld.Entity_ID = e.Entity_ID 
INNER JOIN Collections.Building AS cbp
ON bld.Building_ID = cb.BuildingId
INNER JOIN Collections.BuildingProfile AS cbpro
ON cbp.BuildingPolicyId = cbpro.BuildingPolicyId

WHERE
bld.Building_Active = 1
AND e.Active = 1

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

等风来 2025-01-18 06:11:18

使用“萨拉米技术”来隔离意外行的来源。我的意思是,通过一一省略每个连接(以及与该连接相关的任何列引用),可以像意大利腊肠一样减少查询。

例如,从屏蔽与 Collections.BuildingProfile 的连接开始:

SELECT
      e.Entity_Number
    , bld.Building_Name
    , cbp.PolicyId
    , cbp.Description
    , cbp.StartDate
    , cbp.EndDate
FROM dbo.buildings AS bld
INNER JOIN dbo.entities AS e ON bld.Entity_ID = e.Entity_ID
INNER JOIN Collections.Building AS cbp ON bld.Building_ID = cbp.BuildingId
-- INNER JOIN Collections.BuildingProfile AS cbpro ON cbp.BuildingPolicyId = cbpro.BuildingPolicyId
WHERE bld.Building_Active = 1
    AND e.Active = 1

这是否会删除意外的列?如果没有,那么尝试:

SELECT
      e.Entity_Number
    , bld.Building_Name
    --, cbp.PolicyId
    --, cbp.Description
    --, cbp.StartDate
    --, cbp.EndDate
FROM dbo.buildings AS bld
INNER JOIN dbo.entities AS e ON bld.Entity_ID = e.Entity_ID
--INNER JOIN Collections.Building AS cbp ON bld.Building_ID = cbp.BuildingId
--INNER JOIN Collections.BuildingProfile AS cbpro ON cbp.BuildingPolicyId = cbpro.BuildingPolicyId
WHERE bld.Building_Active = 1
    AND e.Active = 1

最终通过屏蔽每个连接(以及对该表的任何相关列引用),您将发现哪个表正在产生意外的行乘法。

一旦确定了该表,我建议您重新考虑您对该表的连接方式所做的所有假设。例如,您声明“据我所知,每个实体只有一项有效策略。” 这真的是这样吗?

一旦您知道问题从哪里开始,您就可以重新考虑如何在查询中实际使用该数据,您应该更接近解决方案。例如,也许您需要在连接中添加更多条件,或者您需要连接子查询而不是直接连接到表。

注意:

  • 查询似乎不需要 Collections.BuildingProfile,为什么不省略它
    反正?
  • 在 select 子句中重新格式化“逗号优先”有助于简化“萨拉米技术”的使用

Use the "salami technique" to isolate where the unexpected rows come from. What I mean by this is that you cut down the query like a salami by omitting each join (and any column references related to that join) one by one.

e.g. start with masking the join to Collections.BuildingProfile:

SELECT
      e.Entity_Number
    , bld.Building_Name
    , cbp.PolicyId
    , cbp.Description
    , cbp.StartDate
    , cbp.EndDate
FROM dbo.buildings AS bld
INNER JOIN dbo.entities AS e ON bld.Entity_ID = e.Entity_ID
INNER JOIN Collections.Building AS cbp ON bld.Building_ID = cbp.BuildingId
-- INNER JOIN Collections.BuildingProfile AS cbpro ON cbp.BuildingPolicyId = cbpro.BuildingPolicyId
WHERE bld.Building_Active = 1
    AND e.Active = 1

Does this remove the unexpected columns? If not then try:

SELECT
      e.Entity_Number
    , bld.Building_Name
    --, cbp.PolicyId
    --, cbp.Description
    --, cbp.StartDate
    --, cbp.EndDate
FROM dbo.buildings AS bld
INNER JOIN dbo.entities AS e ON bld.Entity_ID = e.Entity_ID
--INNER JOIN Collections.Building AS cbp ON bld.Building_ID = cbp.BuildingId
--INNER JOIN Collections.BuildingProfile AS cbpro ON cbp.BuildingPolicyId = cbpro.BuildingPolicyId
WHERE bld.Building_Active = 1
    AND e.Active = 1

Eventually by masking out each join (and any related column references to that table) you will discover which table is producing the unexpected multiplication of rows.

Once that table is identified I suggest you reconsider all assumptions you have made about how that table had been joined. For example, you state that " From what I understand there is only one active policy per entity." Is that really true?

Once you know where the problem starts, and you reconsider how that data should actually be used within the query, you should be closer to a solution. e.g. perhaps you need more conditions in the join, or you need to join a subquery instead of directly to the table.

Note:

  • Collections.BuildingProfile does not seem needed by the query, why not omit it
    anyway?
  • reformatting for "comma first" in the select clause helps simplify use of the "salami technique"
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文