对于不存在的值强制值为 0
小组，我将尽力解释这一点，我希望它（在某种程度上）有意义。我正在从我创建的视图中提取数据，该视图告诉我“赞助商”客户类型以及按 CustomerType 划分的这些帐户中有多少在 1 年、2 年和 3 年后处于非活动状态。例如：
SponsorID | CustomerType | ExpiredAfter | Total
1234 D 1 Year 5
1234 RA 1 Year 7
1234 CA 1 Year 3
1234 D 2 Years 2
1234 RA 2 Years 4
1234 CA 2 Years 1
1234 D 3 Years 8
1234 RA 3 Years 1
1234 CA 3 Years 6
我遇到的问题是 SponsorID 可能没有任何 1 年后过期的 RA，因此数据中没有显示任何内容。因此，我不是得到：
SponsorID | CustomerType | ExpiredAfter | Total
1235 D 1 Year 5
1235 RA 1 Year 0
1235 CA 1 Year 3
我得到：
SponsorID | CustomerType | ExpiredAfter | Total
1235 D 1 Year 5
1235 CA 1 Year 3
再次，不确定这是否有意义，但我想要做的是包含所有 ExpiredAfter 的所有客户类型，无论是否有值。如果没有值，我只想强制为 0 或 Null。我需要带有 0 的数据行用于报告目的。
有什么建议吗？
Group, I am going to try and explain this as best I can, and I hope it makes (some) sense. I am pulling data from a view I have created that tells me a "Sponsor's" customer types and how many of those accounts by CustomerType are inactive after 1 year, 2 years and 3 years. For example:
SponsorID | CustomerType | ExpiredAfter | Total
1234 D 1 Year 5
1234 RA 1 Year 7
1234 CA 1 Year 3
1234 D 2 Years 2
1234 RA 2 Years 4
1234 CA 2 Years 1
1234 D 3 Years 8
1234 RA 3 Years 1
1234 CA 3 Years 6
The problem I am having is a SponsorID might not have any RA that ExpiredAfter 1 year, so nothing shows up in the data. So instead of getting:
SponsorID | CustomerType | ExpiredAfter | Total
1235 D 1 Year 5
1235 RA 1 Year 0
1235 CA 1 Year 3
I get:
SponsorID | CustomerType | ExpiredAfter | Total
1235 D 1 Year 5
1235 CA 1 Year 3
Again, not sure if any of this makes any sense, but what I want to do is include all customer types for all ExpiredAfter regardless if there are values or not. If there isn't a value I just want to force a 0 or Null. I need that data row with 0 for reporting purposes.
Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
听起来您需要使用
LEFT JOIN而不是INNER JOIN。It sounds like you need to use a
LEFT JOINinstead of anINNER JOIN.您可能还必须使用 ISNULL(total, 0) 或 COALESCE(total, 0)，具体取决于您使用的 DBMS 和您的查询（如 womp 所述）...因为如果计算 TOTAL，则具有空值从 LEFT JOIN 仍然不会得到 0 值。
You may also have to use ISNULL(total, 0) or COALESCE(total, 0) depending on which DBMS you are using, and your query (as stated by womp)... because if TOTAL is calculated, then having a null value from a LEFT JOIN would still leave you without a 0 value.
在我看来，您正在寻求积极的报道。尝试使用交叉连接。交叉联接将一个表中的所有行链接到另一表中的所有行，从而将所有可能的结果作为输出
Seems to me like you are seeking positive reporting. Try using a cross join. Cross joins link all rows from one table to all rows in the other table thereby giving all possible results as output