我有一个oracle数据库表，它有一组重复5次的列。与下面的示例类似，在同一个表中，列（col1\u a到col1\u e）将存在5次（除了列的名称），每组列中的数据类型将相同，但其中一组（col1\u a到col1\u e）中的数据类型不相同。
Cli_Id,Country,Name,COL1_A,COL1_B,COL1_C,COL1_D,COL1_E,COL2_A,COL2_B,COL2_C,COL2_D,COL2_E,COL3_A COL3_B,..
1 Test1 Lo1 1 2 3 4 5 1 2 3 4 5
2 Test2 Lo2 5 6 7 8 9 5 3 3 4 5
3 Test3 Lo3 10 20 30 40 50 5 3 3 4 5
4 Test4 Lo4 11 22 32 42 52 53 3 3 4 5
5 Test5 Lo5 11 22 32 42 52 11 22 32 42 52我需要编写一个查询来检索列集合之间包含重复项的所有行。
预期的结果
Cli_Id,Country,Name,COL1_A,COL1_B,COL1_C,COL1_D,COL1_E,COL2_A,COL2_B,COL2_C,COL2_D,COL2_E,COL3_A COL3_B,..
1 Test1 Lo1 1 2 3 4 5 1 2 3 4 5
5 Test5 Lo5 11 22 32 42 52 11 22 32 42 52我写了一个有几个工会，但可能会影响性能，因为我们打算增加到8集。因此，我们正在寻找一种更简单的方法来编写这个查询。
请你建议一个简单的查询，记住它应该是有效的，不要花太长时间运行。
Select distinct CLi_id,Country,Name
from Table1
Where COL1_A = COL2_A
AND COL1_B = COL2_B
AND COL1_C = COL2_C
AND COL1_D = COL2_D
AND COL1_E = COL2_E
UNION
Select distinct CLi_id,Country,Name
from Table1
Where COL1_A = COL3_A
AND COL1_B = COL3_B
AND COL1_C = COL3_C
AND COL1_D = COL3_D
AND COL1_E = COL3_E
UNION
Select distinct CLi_id,Country,Name
from Table1
Where COL1_A = COL4_A
AND COL1_B = COL4_B
AND COL1_C = COL4_C
AND COL1_D = COL4_D
AND COL1_E = COL4_E
UNION
Select distinct CLi_id,Country,Name
from Table1
Where COL1_A = COL5_A
AND COL1_B = COL5_B
AND COL1_C = COL5_C
AND COL1_D = COL5_D
AND COL1_E = COL5_E
UNION
Select distinct CLi_id,Country,Name
from Table1
Where COL2_A = COL3_A
AND COL2_B = COL3_B
AND COL2_C = COL3_C
AND COL2_D = COL3_D
AND COL2_E = COL3_E
UNION
Select distinct CLi_id,Country,Name
from Table1
Where COL2_A = COL4_A
AND COL2_B = COL4_B
AND COL2_C = COL4_C
AND COL2_D = COL4_D
AND COL2_E = COL4_E
UNION
Select distinct CLi_id,Country,Name
from Table1
Where COL2_A = COL5_A
AND COL2_B = COL5_B
AND COL2_C = COL5_C
AND COL2_D = COL5_D
AND COL2_E = COL5_E
UNION
Select distinct CLi_id,Country,Name
from Table1
Where COL3_A = COL4_A
AND COL3_B = COL4_B
AND COL3_C = COL4_C
AND COL3_D = COL4_D
AND COL3_E = COL4_E
UNION
Select distinct CLi_id,Country,Name
from Table1
Where COL3_A = COL5_A
AND COL3_B = COL5_B
AND COL3_C = COL5_C
AND COL3_D = COL5_D
AND COL3_E = COL5_E
UNION
Select distinct CLi_id,Country,Name
from Table1
Where COL4_A = COL5_A
AND COL4_B = COL5_B
AND COL4_C = COL5_C
AND COL4_D = COL5_D
AND COL4_E = COL5_E
3条答案
按热度按时间bjp0bcyl1#
你可以用
OR以及IN具体如下：rta7y2nd2#
unpivot data，按列和值分组，取min（count）等于序列数的id（在示例3中，将当前数据改为5，将来改为8）：
演示
编辑：
当一行至少有两组相同的列时，我会认为它是重复的
因此，我们必须加入未插入的数据：
D小提琴
u4dcyp6a3#
由于要比较与标识属性的跨级别相对应的列级别值，因此可以使用
UNION ALL以乐观的方式。不需要像您在查询中提到的那样连接列。下面的查询将为您提供行级属性，
下面的查询将为您提供列级属性，