当其中两行需要唯一时选择三行 (SQL)

Question

首先，我使用的是 Oracle 10g Express

所以我想选择三列：

[domain_name] [index_path] [collection_name]

现在有两列我想要唯一：

[domain_name] [index_path]

所以我的问题是我基本上如何：

select unique domain_name, index_path from TABLENAMEHERE

同时选择列 [collection_name]

Answer 1

如果domain_name、index_path 的某些组合有多行，那么您希望从这些行中看到collection_name 的什么值？如果您不介意哪个值，请执行以下操作：

SELECT domain_name, index_path, MIN(collection_name) collection_name
FROM TABLENAMEHERE
GROUP BY domain_name, index_path;

Answer 2

select domain_name, index_path, collection_name
  from my_table outr
       inner join 
         (select domain_name, index_path, collection_name, 
                 max(gen_timestamp) 
                    over (partition by domain_name, index_path) gen_timestamp
            from my_table) innr
 where outr.domain_name = innr.domain_name
   and outr.index_path  = innr.index_path
   and outr.collection_name = innr.collection_name
   and outr.gen_timestamp   = innr.gen_timestamp

Answer 3

只需在查询中将 unique 替换为 distinct 即可。

哦...

那应该是这样的：

select [domain_name], [index_path], min([collection_name]) 
from TABLENAMEHERE
group by [domain_name], [index_path]

Answer 4

您可以按domain_name 和index_path 进行分组，并在每组中包含一个（最小值或最大值）collection_name 值。