问题描述
我的桌子看起来像这样.
My Table looks like this.
Id | Name | Ref | Date | From
10 | Ant | 100 | 2017-02-02 | David
10 | Ant | 300 | 2016-01-01 | David
2 | Cat | 90 | 2017-09-09 | David
2 | Cat | 500 | 2016-02-03 | David
3 | Bird | 150 | 2017-06-28 | David
这是我想要的结果.
Id | Name | Ref | Date | From
3 | Bird | 150 | 2017-06-28 | David
2 | Cat | 500 | 2016-02-03 | David
10 | Ant | 300 | 2016-01-01 | David
我的目标是每个Id的最高引用，按订购日期的顺序排序.
My target is the highest Ref per Id, ordered by Order Date desc.
请告诉我有关如何使用pl/sql编写sql查询的信息.
Could you please tell me about how to write a sql query using pl/sql.
推荐答案
这种要求(您需要以一列为单位的最大或最小值，以另一列为分组，但需要最大或最小值行中的所有数据) 分析功能的用途差不多.我使用了row_number-如果可能建立联系，则需要澄清分配(请参见问题下方的我的评论)，然后根据具体情况，另一个分析函数可能更合适-也许是rank().
This kind of requirement (where you need the max or min by one column, grouped by another, but you need all the data from the max or min row) is pretty much what analytic functions are for. I used row_number - if ties are possible, you need to clarify the assignment (see my Comment under your question), and depending on the details, another analytic function may be more appropriate - perhaps rank().
with
my_table ( id, name, ref, dt, frm ) as (
select 10, 'Ant' , 100, date '2017-02-02', 'David' from dual union all
select 10, 'Ant' , 300, date '2016-01-01', 'David' from dual union all
select 2, 'Cat' , 90, date '2017-09-09', 'David' from dual union all
select 2, 'Cat' , 500, date '2016-02-03', 'David' from dual union all
select 3, 'Bird', 150, date '2017-06-28', 'David' from dual
)
-- End of simulated table (for testing purposes only, not part of the solution).
-- SQL query begins BELOW THIS LINE.
select id, name, ref, dt, frm
from (
select id, name, ref, dt, frm,
row_number() over (partition by id order by ref desc, dt desc) as rn
from my_table
)
where rn = 1
order by dt desc
;
ID NAME REF DT FRM
-- ---- --- ---------- -----
3 Bird 150 2017-06-28 David
2 Cat 500 2016-02-03 David
10 Ant 300 2016-01-01 David
这篇关于如何编写sql查询以选择一行中具有最大值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！