从oracle中的重复行中选择两个不同列的唯一值

Question

表1

school_Name Student_Name Class_ID    
AMM          joe         AMM-1-1-1
AMM          joe         AMM-1-1-2
AMM          Adam        AMM-1-1-1
AMM          Adam        AMM-1-1-2
AMM          Nancy       AMM-1-2-1
AMM          Nancy       AMM-1-2-2
AMM          Albert      AMM-1-2-1
AMM          Albert      AMM-1-2-2
IRB          Frank       IRB-1-1-1
IRB          Frank       IRB-1-1-2
IRB          Mike        IRB-1-1-1
IRB          Mike        IRB-1-1-2

您好，我在oracle中有上面类似的表格，并试图选择唯一的class_id (1)作为该校学生姓名的第一个匹配。有谁能帮帮忙。

select语句的预期输出如下：

结果

school_Name Student_Name Class_ID    
AMM          joe         AMM-1-1-1
AMM          Adam        AMM-1-1-2
AMM          Nancy       AMM-1-2-1
AMM          Albert      AMM-1-2-2
IRB          Frank       IRB-1-1-1
IRB          Mike        IRB-1-1-2

已在下面尝试，但仍未按预期工作。

select 
  school_name,
  student_name,
  class_id, 
from 
(
  select
    school_name,
    student_name,
    class_id,
    row_number() over (partition by class_id order by student_name ASC) rn
  from studentdata_view
) where rn = 1;

Answer 1

您需要更改分区by子句，同时删除了第一个之前的多余逗号

select school_name,student_name,class_id from 
(
   select school_name,student_name,class_id, row_number() over (partition by 
   school_name,student_name order by class_id ASC) rn from studentdata_view
)A where rn = 1;

Answer 2

查询中的逗号过多：

select 
  school_name,
  student_name,
  class_id -- <=== it was here; I removed it 
from 
(
  select
    school_name,
    student_name,
    class_id,
    row_number() over (partition by class_id order by student_name ASC) rn
  from studentdata_view
) where rn = 1;

我还在partition by子句中将full_class_id更改为class_id。