在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屋！