我有一个像这样的表:

 ID |  Val   |  Kind
----------------------
 1  |  1337  |   2
 2  |  1337  |   1
 3  |   3    |   4
 4  |   3    |   4


我想制作一个SELECT,它将仅返回每个Val的第一行,并按Kind排序。 br />示例输出:

 ID |  Val   |  Kind
----------------------
 2  |  1337  |   1
 3  |   3    |   4


如何构建此查询?

评论

为什么3 | 3 | 4而不是4 | 3 | 4-抢七是什么?或者您不在乎吗?

@JackDouglas实际上我有一个ORDER BY ID DESC,但这与问题无关。在此示例中,我不在乎。

#1 楼

此解决方案也使用keep,但也可以为每个组简单地计算valkind而不使用子查询:




select min(id) keep(dense_rank first order by kind) id
     , val
     , min(kind) kind
  from mytable
 group by val;


ID |  VAL | KIND
-: | ---: | ---:
 3 |    3 |    4
 2 | 1337 |    1



dbfiddle此处

KEEP…FIRST和KEEP…LAST是特定于Oracle的聚合功能—您可以在Oracle文档中阅读有关的内容,或在ORACLE_BASE上:


FIRST和LAST函数可用于从有序序列中返回第一个或最后一个值


#2 楼

使用公用表表达式(CTE)和诸如ROW_NUMBER之类的窗口/排序/分区功能。从1到N的数字。PARTITIONBY表示,每当Val的值更改时,它应从1重新开始,而我们希望按最小的Kind值对行进行排序。

WITH ORDERED AS
(
SELECT
    ID
,   Val
,   kind
,   ROW_NUMBER() OVER (PARTITION BY Val ORDER BY Kind ASC) AS rn
FROM
    mytable
)
SELECT
    ID
,   Val
,   Kind
FROM
    ORDERED
WHERE
    rn = 1;


以上方法应适用于已实现ROW_NUMBER()函数的所有RDBMS。如mik的答案所示,Oracle具有一些优雅的功能,通常会产生比该答案更好的性能。

#3 楼

bilinkc的解决方案效果很好,但我想我也应该抛弃我。它具有相同的成本,但可能更快(或更慢,我尚未测试)。区别在于它使用First_Value而不是Row_Number。由于我们只对第一个值感兴趣,因此在我看来,它更简单。

SELECT ID, Val, Kind FROM
(
   SELECT First_Value(ID) OVER (PARTITION BY Val ORDER BY Kind) First, ID, Val, Kind 
   FROM mytable
)
WHERE ID = First;


测试数据。 >
如果您愿意,这里是等效的CTE。

--drop table mytable;
create table mytable (ID Number(5) Primary Key, Val Number(5), Kind Number(5));

insert into mytable values (1,1337,2);
insert into mytable values (2,1337,1);
insert into mytable values (3,3,4);
insert into mytable values (4,3,4);


评论


+1,但我只是想强调一下,除非id唯一,否则您的答案和billinkc的逻辑上并不相同。

–杰克·道格拉斯(Jack Douglas)
2011年9月30日19:01在

@杰克·道格拉斯-是的,我以为是。

–雷·里菲尔(Leigh Riffel)
2011年10月1日,0:02

#4 楼

您可以使用keep从每个组中选择一个id




select *
from mytable
where id in ( select min(id) keep (dense_rank first order by kind, id)
              from mytable
              group by val );


ID |  VAL | KIND
-: | ---: | ---:
 2 | 1337 |    1
 3 |    3 |    4



dbfiddle在这里

#5 楼

SELECT MIN(MyTable01.Id) as Id,
       MyTable01.Val     as Val,
       MyTable01.Kind    as Kind 
  FROM MyTable MyTable01,                         
       (SELECT Val,MIN(Kind) as Kind
          FROM MyTable                   
      GROUP BY Val) MyTableGroup
WHERE MyTable01.Val  = MyTableGroup.Val
  AND MyTable01.Kind = MyTableGroup.Kind
GROUP BY MyTable01.Val,MyTable01.Kind
ORDER BY Id;


评论


由于需要对MyTable进行两次扫描,因此这将比其他答案效率低很多。

– a_horse_with_no_name
13年11月22日在9:49

仅当优化程序从字面上接受书面查询时,这才是正确的。更高级的优化器可以查看意图(每组行),并通过单个表访问来制定计划。

–保罗·怀特♦
15年5月26日在17:13

#6 楼

从表名t1中选择* from(选择t1。*,ROW_NUMBER()OVER(PARTITION BY Val ORDER BY Val desc)作为seqnum)其中seqnum = 1;