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

First of all I am using Oracle 10g Express

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

So there are three columns I want to select:

所以我要选择三列:

[domain_name] [index_path] [collection_name]

[domain_name][index_path][collection_name]

Now there are two columns that I want to be unique:

现在有两列是我想要唯一的:

[domain_name] [index_path]

[domain_name][index_path]

So my issue is how do I basically:

所以我的问题是:

select unique domain_name, index_path from TABLENAMEHERE

while also selecting the column [collection_name]

同时选择列[collection_name]

4 个解决方案

#1

If there are multiple rows for some combinations of domain_name, index_path then what value of collection_name would you want to see from those rows? If you don't mind which value then do:

如果domain_name、index_path的某些组合有多个行，那么您希望从这些行中看到collection_name的什么值?如果你不介意哪个价值，那就做:

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

#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

#3

Just replace unique with distinct in your query.

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

Oh...

哦……

Then it should be like this:

那么它应该是这样的:

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

#4

You can group by the domain_name and index_path and include ONE (min or max) value of collection_name per group.

您可以根据domain_name和index_path进行分组，并在每个组中包含collection_name的一个(最小值或最大值)值。

#1