oracle查询以获取同一表中具有重复列集的所有行

zbq4xfa0  于 2021-07-24  发布在  Java
关注(0)|答案(3)|浏览(355)

我有一个oracle数据库表,它有一组重复5次的列。与下面的示例类似,在同一个表中,列(col1\u a到col1\u e)将存在5次(除了列的名称),每组列中的数据类型将相同,但其中一组(col1\u a到col1\u e)中的数据类型不相同。

  1. 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,..
  2. 1 Test1 Lo1 1 2 3 4 5 1 2 3 4 5
  3. 2 Test2 Lo2 5 6 7 8 9 5 3 3 4 5
  4. 3 Test3 Lo3 10 20 30 40 50 5 3 3 4 5
  5. 4 Test4 Lo4 11 22 32 42 52 53 3 3 4 5
  6. 5 Test5 Lo5 11 22 32 42 52 11 22 32 42 52

我需要编写一个查询来检索列集合之间包含重复项的所有行。
预期的结果

  1. 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,..
  2. 1 Test1 Lo1 1 2 3 4 5 1 2 3 4 5
  3. 5 Test5 Lo5 11 22 32 42 52 11 22 32 42 52

我写了一个有几个工会,但可能会影响性能,因为我们打算增加到8集。因此,我们正在寻找一种更简单的方法来编写这个查询。
请你建议一个简单的查询,记住它应该是有效的,不要花太长时间运行。

  1. Select distinct CLi_id,Country,Name
  2. from Table1
  3. Where COL1_A = COL2_A
  4. AND COL1_B = COL2_B
  5. AND COL1_C = COL2_C
  6. AND COL1_D = COL2_D
  7. AND COL1_E = COL2_E
  8. UNION
  9. Select distinct CLi_id,Country,Name
  10. from Table1
  11. Where COL1_A = COL3_A
  12. AND COL1_B = COL3_B
  13. AND COL1_C = COL3_C
  14. AND COL1_D = COL3_D
  15. AND COL1_E = COL3_E
  16. UNION
  17. Select distinct CLi_id,Country,Name
  18. from Table1
  19. Where COL1_A = COL4_A
  20. AND COL1_B = COL4_B
  21. AND COL1_C = COL4_C
  22. AND COL1_D = COL4_D
  23. AND COL1_E = COL4_E
  24. UNION
  25. Select distinct CLi_id,Country,Name
  26. from Table1
  27. Where COL1_A = COL5_A
  28. AND COL1_B = COL5_B
  29. AND COL1_C = COL5_C
  30. AND COL1_D = COL5_D
  31. AND COL1_E = COL5_E
  32. UNION
  33. Select distinct CLi_id,Country,Name
  34. from Table1
  35. Where COL2_A = COL3_A
  36. AND COL2_B = COL3_B
  37. AND COL2_C = COL3_C
  38. AND COL2_D = COL3_D
  39. AND COL2_E = COL3_E
  40. UNION
  41. Select distinct CLi_id,Country,Name
  42. from Table1
  43. Where COL2_A = COL4_A
  44. AND COL2_B = COL4_B
  45. AND COL2_C = COL4_C
  46. AND COL2_D = COL4_D
  47. AND COL2_E = COL4_E
  48. UNION
  49. Select distinct CLi_id,Country,Name
  50. from Table1
  51. Where COL2_A = COL5_A
  52. AND COL2_B = COL5_B
  53. AND COL2_C = COL5_C
  54. AND COL2_D = COL5_D
  55. AND COL2_E = COL5_E
  56. UNION
  57. Select distinct CLi_id,Country,Name
  58. from Table1
  59. Where COL3_A = COL4_A
  60. AND COL3_B = COL4_B
  61. AND COL3_C = COL4_C
  62. AND COL3_D = COL4_D
  63. AND COL3_E = COL4_E
  64. UNION
  65. Select distinct CLi_id,Country,Name
  66. from Table1
  67. Where COL3_A = COL5_A
  68. AND COL3_B = COL5_B
  69. AND COL3_C = COL5_C
  70. AND COL3_D = COL5_D
  71. AND COL3_E = COL5_E
  72. UNION
  73. Select distinct CLi_id,Country,Name
  74. from Table1
  75. Where COL4_A = COL5_A
  76. AND COL4_B = COL5_B
  77. AND COL4_C = COL5_C
  78. AND COL4_D = COL5_D
  79. AND COL4_E = COL5_E
bjp0bcyl

bjp0bcyl1#

你可以用 OR 以及 IN 具体如下:

  1. Select distinct CLi_id,Country,Name
  2. from Table1
  3. Where (COL1_A, COL1_B, COL1_C, COL1_D, COL1_E)
  4. IN ((COL2_A, COL2_B, COL2_C, COL2_D, COL2_E),
  5. (COL3_A, COL3_B, COL3_C, COL3_D, COL3_E),
  6. (COL4_A, COL4_B, COL4_C, COL4_D, COL4_E),
  7. (COL5_A, COL5_B, COL5_C, COL5_D, COL5_E))
  8. OR (COL2_A, COL2_B, COL2_C, COL2_D, COL2_E)
  9. IN ((COL3_A, COL3_B, COL3_C, COL3_D, COL3_E),
  10. (COL4_A, COL4_B, COL4_C, COL4_D, COL4_E),
  11. (COL5_A, COL5_B, COL5_C, COL5_D, COL5_E))
  12. OR (COL3_A, COL3_B, COL3_C, COL3_D, COL3_E)
  13. IN ((COL4_A, COL4_B, COL4_C, COL4_D, COL4_E),
  14. (COL5_A, COL5_B, COL5_C, COL5_D, COL5_E))
  15. OR (COL4_A, COL4_B, COL4_C, COL4_D, COL4_E)
  16. IN ((COL5_A, COL5_B, COL5_C, COL5_D, COL5_E))
展开查看全部
rta7y2nd

rta7y2nd2#

unpivot data,按列和值分组,取min(count)等于序列数的id(在示例3中,将当前数据改为5,将来改为8):
演示

  1. select *
  2. from table1
  3. where cli_id in (
  4. select cli_id from (
  5. select cli_id, col, val, count(1) cnt
  6. from table1
  7. unpivot (val for col in
  8. (COL1_A as 'a', COL1_B as 'b', COL1_C as 'c', COL1_D as 'd', COL1_E as 'e',
  9. COL2_A as 'a', COL2_B as 'b', COL2_C as 'c', COL2_D as 'd', COL2_E as 'e',
  10. COL3_A as 'a', COL3_B as 'b', COL3_C as 'c', COL3_D as 'd', COL3_E as 'e'))
  11. group by cli_id, col, val )
  12. group by cli_id
  13. having min(cnt) = 3)

编辑:
当一行至少有两组相同的列时,我会认为它是重复的
因此,我们必须加入未插入的数据:
D小提琴

  1. with u as (
  2. select cli_id, country, name, ltr, num, val
  3. from table1
  4. unpivot (val for (num, ltr) in (
  5. COL1_A as (1, 'a'), COL1_b as (1, 'b'), COL1_c as (1, 'c'),
  6. COL1_d as (1, 'd'), COL1_e as (1, 'e'),
  7. COL2_A as (2, 'a'), COL2_b as (2, 'b'), COL2_c as (2, 'c'),
  8. COL2_d as (2, 'd'), COL2_e as (2, 'e'),
  9. COL3_A as (3, 'a'), COL3_b as (3, 'b'), COL3_c as (3, 'c'),
  10. COL3_d as (3, 'd'), COL3_e as (3, 'e')
  11. )))
  12. select distinct cli_id, country, name
  13. from u a join u b using (cli_id, country, name, ltr, val)
  14. where a.num < b.num
  15. group by cli_id, country, name, a.num, b.num
  16. having count(1) = 5
展开查看全部
u4dcyp6a

u4dcyp6a3#

由于要比较与标识属性的跨级别相对应的列级别值,因此可以使用 UNION ALL 以乐观的方式。不需要像您在查询中提到的那样连接列。
下面的查询将为您提供行级属性,

  1. select Cli_Id,Country,Name, col_A, col_B, col_C, col_D, count(*) from
  2. (select Cli_Id,Country,Name,COL1_A as col_A,COL1_B as col_B,COL1_C as col_C,COL1_D as col_D,COL1_E as col_E from table
  3. union all
  4. select Cli_Id,Country,Name,COL2_A as col_A,COL2_B as col_B,COL2_C as col_C,COL2_D as col_D,COL2_E as col_E from table
  5. union all
  6. select Cli_Id,Country,Name,COL3_A as col_A,COL3_B as col_B,COL3_C as col_C,COL3_D as
  7. col_D,COL3_E as col_E from table
  8. union all
  9. select Cli_Id,Country,Name,COL4_A as col_A,COL4_B as col_B,COL4_C as col_C,COL4_D as col_D,COL4_E as col_E from table
  10. union all
  11. select Cli_Id,Country,Name,COL5_A as col_A,COL5_B as col_B,COL5_C as col_C,COL5_D as col_D,COL5_E as col_E from table)
  12. group by Cli_Id,Country,Name, col_A, col_B, col_C, col_D having count(*) > 1;

下面的查询将为您提供列级属性,

  1. select distinct t1.* from table t1
  2. inner join
  3. (select Cli_Id,Country,Name, col_A, col_B, col_C, col_D, count(*) from
  4. (select Cli_Id,Country,Name,COL1_A as col_A,COL1_B as col_B,COL1_C as col_C,COL1_D as col_D,COL1_E as col_E from table
  5. union all
  6. select Cli_Id,Country,Name,COL2_A as col_A,COL2_B as col_B,COL2_C as col_C,COL2_D as col_D,COL2_E as col_E from table
  7. union all
  8. select Cli_Id,Country,Name,COL3_A as col_A,COL3_B as col_B,COL3_C as col_C,COL3_D as col_D,COL3_E as col_E from table
  9. union all
  10. select Cli_Id,Country,Name,COL4_A as col_A,COL4_B as col_B,COL4_C as col_C,COL4_D as col_D,COL4_E as col_E from table
  11. union all
  12. select Cli_Id,Country,Name,COL5_A as col_A,COL5_B as col_B,COL5_C as col_C,COL5_D as col_D,COL5_E as col_E from table
  13. group by Cli_Id,Country,Name, col_A, col_B, col_C, col_D having count(*) > 1) t2
  14. on (t1.Cli_Id = t2.Cli_Id);
展开查看全部

相关问题