查找 Teradata SQL 中给定列的哪些行具有不同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13708176/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find which rows have different values for a given column in Teradata SQL
提问by Hatt
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 具有不同的地址。
回答by Olivier Jacot-Descombes
Join the table with itself and give it two different aliases (Aand Bin the following example). 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 Aalias and the Balias in turn.
“小于”比较<可确保您获得 2 个不同的地址，并且不会两次获得相同的 2 个地址代码。<>改为使用“不等于” ，将产生 (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 子句测试附加条件。
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）不同的客户可能很有用。
回答by Yasin Okumu?
This works for PL/SQL:
这适用于 PL/SQL：
select count(*), id,address from table group by id,address having count(*)<2
回答by Gordon Linoff
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
回答by amphibient
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 diffbetween 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
对于每一行，文件的行数与列数一样多。然后我会diff在两个文件之间做一个，假设你在 Unix 上，或者在另一个操作系统上使用一些等效的实用程序来比较它们。如果您有多个记录集（即不止一行），我会在每个文件行之前添加，然后该文件将有 NUM_DB_ROWS * NUM_COLS 行
