在Teradata SQL中查找给定列的哪些行具有不同的值

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

我试图比较来自相同ID的两个地址，看看它们是否匹配。例如：

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

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.

我只想弄清楚每个ID的地址是否匹配。所以在这种情况下，我想只返回ID 3作为地址代码1和2的不同地址。

4 个解决方案

#1

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

加入表格并给它两个不同的别名（A和B）。这允许比较同一表的不同行。

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

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.

“小于”比较 <确保您获得2个不同的地址，并且您没有两次获得相同的2个地址代码。使用“not equal”<> 代替，将产生代码为（1,2）和（2,1）;其中每一个依次为A别名和B别名。

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

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

UPDATE:

更新：

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

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) .

我想这可能有助于找到帐单地址（地址代码= 1作为示例）与递送地址（地址代码= 2）不同的客户。

#2

This works for PL/SQL:

这适用于PL / SQL：

select count(*), id,address from table group by id,address having count(*)<2

#3

You can do this using a group by:

您可以使用以下组来执行此操作：

select id, addressCode
from t
group by id, addressCode
having min(address) <> max(address)

Another way of writing this may seem clearer, but does not perform as well:

另一种写作方式可能看起来更清晰，但表现不佳：

select id, addressCode
from t
group by id, addressCode
having count(distinct address) > 1

#4

Personally, I would print them to a file using Perl or Python in the format

就个人而言，我会使用Perl或Python格式将它们打印到文件中

<COL_NAME>:  <COL_VAL>

for each row so that the file has as many lines as there are columns. Then I'd do a diff between the two files, assuming you are on Unix or compare them using some equivalent utilty on another OS. If you have multiple recordsets (i.e. more than one row), I would prepend to each file row and then the file would have NUM_DB_ROWS * NUM_COLS lines

对于每一行，以便文件具有与列一样多的行。然后我会在两个文件之间进行区分，假设你在Unix上或者在另一个操作系统上使用一些等效的实用程序来比较它们。如果你有多个记录集（即多行），我会在每个文件行前面，然后该文件将有NUM_DB_ROWS * NUM_COLS行

#1