Oracle, Select Distinct columns, with对应的列

Question

我想选择DISTINCTuser_id列，但我也需要相应的列。

结果集需要返回两个role_iddistinctuser_idUnassigned的地位。

我正在使用的查询:

SELECT role_id, user_id, role_code, status_code FROM table where school_id=5 and status_code= 'DRAFT'; 

这是我的表格的一个例子:

ROLE_ID USER_ID SCHOOL_ID CAMPUS_ID ROLE_CODE STATUS_CODE 
1       4        5         7      Unassigned   DRAFT
2       4        5         7        TEST       DRAFT
3       4        5         8        TEST       DRAFT
4       5        5         9      Unassigned  DRAFT
5       5        5         9        TEST       DRAFT
6       5        5         10       TEST       DRAFT

我已经尝试添加基于user_id的组，但我得到ORA-00979。

Answer 1

您可以使用ROW_NUMBER()来标识您想要的行。例如:

select *
from (
select t.*,
row_number() over(partition by user_id order by role_id) as rn
from t
where role_code <> 'Unassigned'
) x
where rn = 1

Answer 2

DISTINCT是跨整个列集的，而不是针对某个特定列的。因此，如果您想获得DISTINCT行而不是Unassigned行，您可以使用:

SELECT DISTINCT
role_id,
user_id,
role_code,
status_code
FROM   table
where  school_id   = 5
and    status_code = 'DRAFT'
and    role_code   != 'Unassigned';

如果您想获得每个user_id的单行，那么您可以使用GROUP BY并找到最小的role_id:

SELECT MIN(role_id) AS role_id,
user_id,
MIN(role_code  ) KEEP (DENSE_RANK FIRST ORDER BY role_id) AS role_code,
MIN(status_code) KEEP (DENSE_RANK FIRST ORDER BY role_id) AS status_code
FROM   table
where  school_id   = 5
and    status_code = 'DRAFT'
and    role_code   != 'Unassigned'
GROUP BY
user_id;