oracle 为Oracle中的列选择最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2173737/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting max value for a column in Oracle
提问by Vinoth
I have a table which has data like this
我有一张表，里面有这样的数据
id test_val type_cd
#-------------------------
101 TEST22 M
102 TEST23 M
103 TEST01 M
103 TEST01 H
104 TEST02 M
104 TEST02 H
105 TEST03 H
I would like to fetch the max(id) for each type_cd and its corresponding test_val in a single row output as below.
我想在单行输出中获取每个 type_cd 及其对应的 test_val 的 max(id) ，如下所示。
The expected output is:
预期的输出是：
M_id M_Test_Val H_id H_Test_Val
#-----------------------------------
104 TEST02 105 TEST03
If I have to fetch only the max(id) for each type_cd I'll have my query like this
如果我只需要为每个 type_cd 获取 max(id) 我就会有这样的查询
select max(case when type_cd='M' then max(id) else null end) as M_id,
max(case when type_cd='H' then max(id) else null end) as H_id
from t1
group by type_cd;
I'm not sure how to get the test_val for the max(id)for each type_cd.
我不确定如何max(id)为每个type_cd.
回答by APC
This is the sort of scenario where analytic functions come into their own....
这是分析函数发挥作用的那种场景......
select type_cd
, id as max_id
, test_val
from (
select type_cd
, id
, test_val
, rank () over (partition by type_cd order by id desc) as rank_id
from your_table
)
where rank_id = 1
/
edit
编辑
The above query doesn't satisfy your need for a single row. Slotting that query into a CTE like this ought to do it...
上面的查询不能满足您对单行的需求。将该查询插入这样的 CTE 应该这样做......
with subq as
( select type_cd
, id as max_id
, test_val
from (
select type_cd
, id
, test_val
, rank () over (partition by type_cd
order by id desc) as rank_id
from your_table
)
where rank_id = 1 )
select m.id as m_id
, m.test_val as m_test_val
, h.id as h_id
, h.test_val as h_test_val
from ( select * from subq where type_cd = 'M') m
join ( select * from subq where type_cd = 'H') h
/
/
回答by Vinoth
I wrote the code as below and it works as expected.
我编写了如下代码，它按预期工作。
select max(case when type_cd='M' then id else null end) as m_id,
max(case when type_cd='M' then test_val else null end) as m_test_val,
max(case when type_cd='H' then id else null end) as h_id,
max(case when type_cd='M' then test_val else null end) as h_test_val
from
( select type_cd ,
id ,
test_val ,
rank () over (partition by type_cd order by id desc) as rank_id
from your_table )
where rank_id = 1;
Not sure if this is the optimal way. But works just as I want it to.
不确定这是否是最佳方式。但就像我想要的那样工作。
