返回不相等的两行中的值。

I'm trying to return the results of a query where two columns columns are not equal to each other.

我试图返回一个查询的结果，其中两个列不相等。

However, when I run the query the results don't seem to be consistent with the actual data.

然而，当我运行查询时，结果似乎与实际数据不一致。

As you can see below, I want to return rows where the RatePlanIDs are different.

正如您可以看到的，我想返回RatePlanIDs不同的行。

RatePlans_Supplemental.PIDs could actually be null, but I never see null results. It also returns duplicates.

RatePlans_Supplemental。PIDs可以是空的，但是我从来没有见过空的结果。它还返回副本。

I've tried this with muliple queries and they all seem to do the same thing.

我尝试过使用多组查询，它们看起来都是一样的。

I've actually verified it's returning wrong data. It's like the query is inserting values if there's a null. Any idea why?

我已经证实它返回了错误的数据。就好像查询插入了一个空值。知道为什么吗?

Update Billing_UnitUsage
Set SuppRateSuccess = 0
FROM         Billing_UnitUsage INNER JOIN
                      RatePlans_Supplemental ON Billing_UnitUsage.SupplementalRateID = RatePlans_Supplemental.UDC_ID AND 
                      NOT(Billing_UnitUsage.RatePlanID = RatePlans_Supplemental.PIDs)

2 个解决方案

#1

Null is "unknown". Any function against unknown (including NOT) returns unknown. In order to get a row to return, your criteria must return TRUE.

零是“未知”。任何针对未知(包括NOT)的函数都返回未知。为了让一行返回，您的条件必须返回TRUE。

Use IS NULL and IS NOT NULL to test for NULL.

Use是NULL，并且不是NULL来测试NULL。

Here's my analogy.

这是我的类比。

If there are two strangers in the room, and you ask, are their names the same? The answer is unknown.

如果房间里有两个陌生人，你会问，他们的名字是一样的吗?答案是未知的。

If you ask, are their names not the same? The answer is unknown.

如果你问，他们的名字不一样吗?答案是未知的。

Without knowing their names, the answer, unfortunately, is always unknown.

不幸的是，在不知道他们的名字的情况下，答案总是未知的。

#2

I added the table aliases just to improve readability.

为了提高可读性，我添加了表别名。

If you don't want to update SuppRateSuccess when PIDs is NULL:

如果您不想在PIDs为空时更新suppresuccess:

Update bu
Set SuppRateSuccess = 0
    FROM Billing_UnitUsage bu
        INNER JOIN RatePlans_Supplemental rs
            ON bu.SupplementalRateID = rs.UDC_ID 
    WHERE bu.RatePlanID <> ISNULL(rs.PIDs, bu.RatePlanID)

If you do want to update SuppRateSuccess when PIDs is NULL (assumes RatePlanID<>0):

如果您确实希望在pid为空时更新suppresuccess(假设RatePlanID<>):

Update bu
Set SuppRateSuccess = 0
    FROM Billing_UnitUsage bu
        INNER JOIN RatePlans_Supplemental rs
            ON bu.SupplementalRateID = rs.UDC_ID 
    WHERE bu.RatePlanID <> ISNULL(rs.PIDs, -bu.RatePlanID)

#1