如何检查特定 ID 的 Oracle 列值是否完全相同？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33092805/
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
How to check Oracle column values are all the same for a specific ID?
提问by tonyf
I am trying to figure out the best way to determine, for a specific ID within an Oracle 11g table that has 5 columns and say 100 rows against this ID, if all the column values are the same for these five columns.
我试图找出最好的方法来确定，对于 Oracle 11g 表中具有 5 列并针对该 ID 说 100 行的特定 ID，如果这五列的所有列值都相同。
For example:
例如：
Table Name: TABLE_DATA
表名：TABLE_DATA
Columns:
列：
TD_ID ID COL1 COL2 COL3 COL4 COL5
-----------------------------------------------------------------------
1 1 1 0 3 2 0
2 1 1 0 3 2 0
3 1 1 0 3 2 0
4 1 1 0 3 2 0
5 1 1 0 3 2 0
6 1 1 0 3 2 0
So based on the above example which is just showing 6 rows for now against the ID:1, I want to check that for all COL1, COL2, COL3, COL4 and COL5 values where ID = 1, tell me if all the values are the same from the very first row right down to the last – if so, then return ‘Y' else return ‘N'.
因此，基于上面的示例，现在仅针对 ID:1 显示 6 行，我想检查 ID = 1 的所有 COL1、COL2、COL3、COL4 和 COL5 值，告诉我是否所有值都是从第一行到最后一行都是相同的——如果是这样，则返回 'Y' 否则返回 'N'。
Given the above example, the result would be ‘Y' but for instance, if TD_ID = 5 and COL3 = 4 then the result would be ‘N', as all the column values are not the same, i.e.:
给定上面的示例，结果将为“Y”，但例如，如果 TD_ID = 5 且 COL3 = 4，则结果将为“N”，因为所有列值都不相同，即：
TD_ID ID COL1 COL2 COL3 COL4 COL5
-----------------------------------------------------------------------
1 1 1 0 3 2 0
2 1 1 0 3 2 0
3 1 1 0 3 2 0
4 1 1 0 3 2 0
5 1 1 0 4 2 0
6 1 1 0 3 2 0
I'm just not sure what the fastest approach to determine this is, as the table I am looking at may have more than 2000 rows within the table for a specific ID.
我只是不确定确定这一点的最快方法是什么，因为我正在查看的表中针对特定 ID 的表可能有 2000 多行。
回答by Massimiliano Piccinini
You may also try this :
你也可以试试这个：
Select ID
, case when count(distinct COL1 || COL2 || COL3 || COL4 || COL5) > 1
then 'N'
else 'Y' end RESULT
From TABLE_DATA
Group by id;
In this way you group by id and counts how many distinct combination are there. If only 1 , so all the rows have the same set of values, otherwise it don't.
通过这种方式，您可以按 id 分组并计算有多少不同的组合。如果只有 1 ，则所有行都具有相同的值集，否则没有。
回答by PM 77-1
See if the following is fast enough for you:
看看以下是否对你来说足够快：
SELECT ID, CASE WHEN COUNT(*) > 1 THEN 'No' ELSE 'Yes' END As "Result"
FROM (SELECT DISTINCT ID, COL1, COL2, COL3, COL4, COL5
FROM Table_Data) dist
GROUP BY ID
回答by AndrewMcCoist
Here's a little query, you might wanna try out (eventually, you just could try figuring out a better MINUSstatement for you):
这是一个小问题，您可能想尝试一下（最终，您可以尝试MINUS为您找出更好的语句）：
SELECT
CASE
WHEN ( -- select count of records from a subquery
SELECT
COUNT(1)
FROM
( -- select all rows where id = 1
SELECT
td.col1
,td.col2
,td.col3
,td.col4
,td.col5
FROM
table_data td
WHERE
td.id = 1
MINUS -- substract the first row of the table with id = 1
SELECT
td.col1
,td.col2
,td.col3
,td.col4
,td.col5
FROM
table_data td
WHERE
td.id = 1
AND ROWNUM = 1
)
) = 0 -- check if subquery's count equals 0
AND EXISTS ( -- and exists at least 1 row in the table with id = 1
SELECT
1
FROM
table_data td
WHERE
td.id = 1
AND ROWNUM = 1
) THEN 'Y'
ELSE 'N'
END AS equal
FROM
dual
