如何删除Oracle SQL查询中的重复行 [英] How to remove repeated lines in an Oracle SQL query

问题描述

我有一个查询

SELECT t1.table_id FROM TABLE_ONE t1, COMMON_TABLE cmn
  WHERE
        t1.table_name = 'TEST_ONE'
    AND t1.table_id = cmn.common_id
    AND cmn.column_one > 20
    AND cmn.column_two <= 30
UNION
  SELECT t2.table_id FROM TABLE_ONE t2, COMMON_TABLE cmn
  WHERE
        t2.table_name = 'TEST_TWO'
    AND t2.table_id = cmn.common_id
    AND cmn.column_one > 20
    AND cmn.column_two <= 30
UNION
  SELECT t3.table_id FROM TABLE_ONE t3, COMMON_TABLE cmn
  WHERE
        t3.table_name = 'TEST_THREE'
    AND t3.table_id = cmn.common_id
    AND cmn.column_one > 20
    AND cmn.column_two <= 30
UNION
  SELECT tc1.table_id FROM TABLE_CHILD_ONE tc1, TABLE_ONE t1, COMMON_TABLE cmn
  WHERE
        tc1.table_name = 'TEST_CHILD_ONE'
    AND tc1.table_id = t1.table_id
    AND t3.table_id = cmn.common_id
    AND cmn.column_one > 20
    AND cmn.column_two <= 30

我想删除重复的行

AND cmn.column_one > 20
AND cmn.column_two <= 30

，并放在最后.你能帮我吗?答案中提到的使用联接的问题是，我也不确定如何将联接与子表一起使用.我可以选择使用嵌套选择.但是试图使用连接以获得更好的性能.

and put it at the end. Can you kindly help me out? The problem of using joins as mentioned in answers is, I m not sure as how to put joins with child tables too. I had the option of using nested select. But was trying to use joins for better performance.

推荐答案

您可以使用以下联接语法重写此查询:

You can rewrite this query with join syntax like :

SELECT DISTINCT t.table_id
FROM   TABLE_ONE t
       JOIN COMMON_TABLE cmn
         ON t.table_id = cmn.common_id
WHERE  t.table_name IN( 'TEST_ONE', 'TEST_TWO', 'TEST_THREE' )
       AND cmn.column_one > 20
       AND cmn.column_two <= 30 

DISTINCT将删除重复的记录.

您可以包括以下子表:

SELECT DISTINCT t.table_id
FROM   TABLE_ONE t
       JOIN COMMON_TABLE cmn
         ON t.table_id = cmn.common_id
       JOIN TABLE_CHILD_ONE tc
         ON t.table_id = tc.table_id
WHERE  ( tc.table_name IN( 'TEST_CHILD_ONE', 'TEST_CHILD_TWO', 'TEST_CHILD_THREE' )
          OR t.table_name IN( 'TEST_ONE', 'TEST_TWO', 'TEST_THREE' ) )
       AND cmn.column_one > 20
       AND cmn.column_two <= 30 

这篇关于如何删除Oracle SQL查询中的重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！