如何删除“重复"从视图行? [英] How do I remove "duplicate" rows from a view?
问题描述
当我加入主表时，我有一个工作正常的视图:
I have a view which was working fine when I was joining my main table:
LEFT OUTER JOIN OFFICE ON CLIENT.CASE_OFFICE = OFFICE.TABLE_CODE.
但是我需要添加以下连接:
However I needed to add the following join:
LEFT OUTER JOIN OFFICE_MIS ON CLIENT.REFERRAL_OFFICE = OFFICE_MIS.TABLE_CODE
虽然我添加了 DISTINCT，但我仍然得到一个重复"行.我说重复"是因为第二行有不同的值.
Although I added DISTINCT, I still get a "duplicate" row. I say "duplicate" because the second row has a different value.
但是，如果我将 LEFT OUTER 更改为 INNER JOIN，我将丢失具有这些重复"行的客户端的所有行.
However, if I change the LEFT OUTER to an INNER JOIN, I lose all the rows for the clients who have these "duplicate" rows.
我做错了什么?如何从我的视图中删除这些重复"行?
What am I doing wrong? How can I remove these "duplicate" rows from my view?
这个问题不适用于这种情况:
This question is not applicable in this instance:
推荐答案
如果行有任何不同的列，DISTINCT 将无济于事.显然，您要加入的其中一个表在另一个表中的一行中有多个行.要恢复一行，您必须消除要加入的表中的其他多行.
DISTINCT won't help you if the rows have any columns that are different. Obviously, one of the tables you are joining to has multiple rows for a single row in another table. To get one row back, you have to eliminate the other multiple rows in the table you are joining to.
执行此操作的最简单方法是增强您的 where 子句或 JOIN 限制，使其仅连接到您想要的单个记录.通常这需要确定一个规则，该规则将始终从另一个表中选择正确"的条目.
The easiest way to do this is to enhance your where clause or JOIN restriction to only join to the single record you would like. Usually this requires determining a rule which will always select the 'correct' entry from the other table.
让我们假设您有一个简单的问题，例如:
Let us assume you have a simple problem such as this:
Person: Jane
Pets: Cat, Dog
如果您在此处创建一个简单的联接，您将收到 Jane 的两条记录:
If you create a simple join here, you would receive two records for Jane:
Jane|Cat
Jane|Dog
如果您的观点是列出人和宠物的所有组合，这是完全正确的.但是，如果您的视图应该列出养宠物的人，或者列出人们并显示他们的一只宠物，那么您就遇到了现在的问题.为此，您需要一个规则.
This is completely correct if the point of your view is to list all of the combinations of people and pets. However, if your view was instead supposed to list people with pets, or list people and display one of their pets, you hit the problem you have now. For this, you need a rule.
SELECT Person.Name, Pets.Name
FROM Person
LEFT JOIN Pets pets1 ON pets1.PersonID = Person.ID
WHERE 0 = (SELECT COUNT(pets2.ID)
FROM Pets pets2
WHERE pets2.PersonID = pets1.PersonID
AND pets2.ID < pets1.ID);
这样做是应用规则将连接中的 Pets 记录限制为具有最低 ID 的 Pet(在 Pets 表中的第一个).WHERE 子句本质上是说那里没有属于同一个人且 ID 值较低的宠物).
What this does is apply a rule to restrict the Pets record in the join to to the Pet with the lowest ID (first in the Pets table). The WHERE clause essentially says "where there are no pets belonging to the same person with a lower ID value).
这将产生一个记录结果:
This would yield a one record result:
Jane|Cat
您需要应用于视图的规则取决于您拥有的列中的数据，以及列中应显示多条"记录中的哪一条.但是，这最终会隐藏一些数据，这可能不是您想要的.例如，上面的规则隐藏了 Jane 有一只狗的事实.当这不正确时，它看起来好像简只有一只猫.
The rule you'll need to apply to your view will depend on the data in the columns you have, and which of the 'multiple' records should be displayed in the column. However, that will wind up hiding some data, which may not be what you want. For example, the above rule hides the fact that Jane has a Dog. It makes it appear as if Jane only has a Cat, when this is not correct.
如果您开始过滤掉有效数据，您可能需要重新考虑视图的内容，以及您试图用视图实现的目标.
You may need to rethink the contents of your view, and what you are trying to accomplish with your view, if you are starting to filter out valid data.
这篇关于如何删除“重复"从视图行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
