大于 一个数 oracle 标记,一道SQL面试题：oracle, 分类汇总, 标记相同id第一条有效...

试题

表数据：table1：

id     name    name_wt cert   cert_wt

--------------------------------------------

A12510

A2477

Ba3Ha11

Bb999C10

查询结果：table2

id     name    name_wt cert   cert_wt

--------------------------------------------

A1277

Ba3C10

请根据table2的要求，通过编写SQL语句实现：根据ID分组，每个字段值对应的WT最小。

答案：

select A.id, A.name, A.name_wt, B.cert, B.cert_wt

from (select *

from (select t1.id,

t1.name,

t1.name_wt,

row_number() over(partition by id order by to_number(t1.name_wt) asc) unit_id

from table1t1)

where unit_id = 1) A

left join (select *

from (select t1.id,

t1.cert,

t1.cert_wt,

row_number() over(partition by id order by to_number(t1.cert_wt) asc) unit_id

from table1t1)

where unit_id = 1) B

on a.id = b.id