代码之家 › 专栏 › 技术社区 › Jacob Nelson

选择三行,其中两行(分组)唯一,另一行由另一列(SQL)过滤

aggregate-functions oracle10g plsql oracle sql

Jacob Nelson · 技术社区 · 14 年前

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

现在有两列我想成为唯一的(作为一个组):

然后我想选择另一列[gen\u timestamp]最近时显示的行。

所以我的问题是我该怎么做:

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

但是不要选择min collection\u name,而是选择[gen\u timestamp]是最近的行。

您需要域名和索引路径的唯一值,还是两者的唯一组合?

两者的独特结合。

对。

这是我现在正在使用的代码,但不太有效:

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 回复 | 直到 14 年前

Rob van Wijk 14 年前

从版本9开始就有一个聚合函数可以满足您的要求。不幸的是,我还没有在你的两个帖子的回复中看到这个。

展示您的问题的表格:

SQL> create table tablenamehere (domain_name,index_path,collection_name,gen_timestamp)
  2  as
  3  select 'A', 'Z', 'a collection name', systimestamp from dual union all
  4  select 'A', 'Z', 'b collection name', systimestamp - 1 from dual union all
  5  select 'A', 'Y', 'c collection name', systimestamp from dual union all
  6  select 'B', 'X', 'd collection name', systimestamp - 2 from dual union all
  7  select 'B', 'X', 'e collection name', systimestamp - 4 from dual union all
  8  select 'B', 'X', 'f collection name', systimestamp from dual
  9  /

Table created.

SQL> SELECT domain_name, index_path, MIN(collection_name) collection_name
  2  FROM TABLENAMEHERE
  3  GROUP BY domain_name, index_path
  4  /

D I COLLECTION_NAME
- - -----------------
A Y c collection name
A Z a collection name
B X d collection name

3 rows selected.

无需对所有行应用分析函数并对这些结果进行筛选:您正在进行聚合,最后一个函数正好完成了您的工作。以下是文档的链接: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions071.htm#sthref1495

SQL> select domain_name
  2       , index_path
  3       , max(collection_name) keep (dense_rank last order by gen_timestamp) collection_name
  4    from tablenamehere
  5   group by domain_name
  6       , index_path
  7  /

D I COLLECTION_NAME
- - -----------------
A Y c collection name
A Z a collection name
B X f collection name

3 rows selected.

当做, 罗伯。

OMG Ponies 14 年前

如果出现重复的gen\u时间戳值,则存在重复的风险:

 SELECT x.domain_name, 
        x.index_path, 
        x.collection_name
   FROM TABLENAMEHERE x
   JOIN (SELECT t.domain_name,
                t.index_path,
                MAX(t.gen_timestamp) AS max_ts
           FROM YOUR_TABLE t
       GROUP BY t.domain_name, t.index_path) y ON y.domain_name = x.domain_name
                                              AND y.index_path = x.index_path
                                              AND y.max_ts = x.gen_timestamp
ORDER BY domain_name, index_path

使用行号(9i+),没有重复的风险:

WITH summary AS (
  SELECT t.domain_name,
         t.index_path,
         t.collection_name,
         ROW_NUMBER() OVER(PARTITION BY t.domain_name,
                                        t.index_path
                               ORDER BY t.gen_timestamp DESC) AS rank
    FROM YOUR_TABLE t)
  SELECT s.domain_name,
         s.index_path,
         s.collection_name
    FROM summary s
   WHERE s.rank = 1
ORDER BY domain_name, index_path

Allan 14 年前

select distinct domain_name, 
                index_path, 
                first(collection_name) over (partition by domain_name, index_path order by gen_timestamp desc) 
from Your_Table