查找 Teradata SQL 中给定列的哪些行具有不同的值 [英] Find which rows have different values for a given column in Teradata SQL

问题描述

我正在尝试比较来自同一 ID 的两个地址，以查看它们是否匹配.例如:

I am trying to compare two addresses from the same ID to see whether they match. For example:

Id  Adress Code     Address
1   1               123 Main
1   2               123 Main
2   1               456 Wall
2   2               456 Wall
3   1               789 Right
3   2               100 Left

我只是想弄清楚每个 ID 的地址是否匹配.因此，在这种情况下，我只想返回 ID 3，因为地址代码 1 和 2 具有不同的地址.

I'm just trying to figure out whether the address for each ID matches. So in this case I want to return just ID 3 as having a different address for Address Code 1 and 2.

推荐答案

将表与自身连接起来，并给它两个不同的别名(下例中的A和B).这允许比较同一表的不同行.

Join the table with itself and give it two different aliases (A and B in the following example). This allows to compare different rows of the same table.

SELECT DISTINCT A.Id
FROM
    Address A
    INNER JOIN Address B
        ON A.Id = B.Id AND A.[Adress Code] < B.[Adress Code]
WHERE
    A.Address <> B.Address

小于"比较 < 确保您获得 2 个不同的地址，并且不会两次获得相同的 2 个地址代码.改用不等于"<>，将产生代码为 (1, 2) 和 (2, 1);依次为 A 别名和 B 别名.

The "less than" comparison < ensures that you get 2 different addresses and you don't get the same 2 address codes twice. Using "not equal" <> instead, would yield the codes as (1, 2) and (2, 1); each one of them for the A alias and the B alias in turn.

join 子句负责行的配对，where 子句测试附加条件.

The join clause is responsible for the pairing of the rows where as the where-clause tests additional conditions.

以上查询适用于任何地址代码.如果要比较地址与特定地址代码，可以将查询更改为

The query above works with any address codes. If you want to compare addresses with specific address codes, you can change the query to

SELECT A.Id
FROM
    Address A
    INNER JOIN Address B
        ON A.Id = B.Id
WHERE                     
    A.[Adress Code] = 1 AND
    B.[Adress Code] = 2 AND
    A.Address <> B.Address

我想这可能有助于找到帐单地址(例如，地址代码 = 1)与送货地址(地址代码 = 2)不同的客户.

I imagine that this might be useful to find customers having a billing address (Adress Code = 1 as an example) differing from the delivery address (Adress Code = 2) .

这篇关于查找 Teradata SQL 中给定列的哪些行具有不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！