以下记录只取其中一笔就可以
1 78 ASIA_ZHANG aa 100000318 华东区 1
2 78 ASIA_ZHANG aa 100000330 上海办 2
3 98 EISLEEPING_LV bb 100000317 华南区 1
4 98 EISLEEPING_LV bb 100000335 深圳办 2
Oracle 实现方法
--EBS 查询有效销售小组与对应人员的语句
select *
from(
SELECT distinct
res.source_id
,res.user_name
,res.source_last_name
,grprel.GROUP_ID
,groups.group_name
,RES.SOURCE_JOB_TITLE
,row_number()
over(PARTITION BY res.source_id,res.user_name,res.source_last_name
ORDER BY res.source_id,res.user_name,res.source_last_name ) rn
FROM JTF_RS_RESOURCE_EXTNS RES
, JTF_RS_ROLE_RELATIONS RREL
, JTF_RS_ROLES_VL ROLE
, jtf_rs_groups_tl groups
, JTF_RS_GROUP_USAGES u
, JTF_RS_GROUP_MEMBERS grprel
WHERE RES.CATEGORY = 'EMPLOYEE' AND ROLE.ROLE_TYPE_CODE in ('SALES','TELESALES','FIELDSALES','PRM')
AND RREL.ROLE_ID = ROLE.ROLE_ID AND groups.language = userenv('LANG')
AND grprel.GROUP_ID = groups.group_id AND nvl(rrel.delete_flag,'N') = 'N'
AND rrel.start_date_active <= sysdate AND nvl(rrel.end_date_active,sysdate) >= sysdate
AND U.group_id = grprel.group_id AND u.usage = 'SALES' AND (ROLE.MEMBER_FLAG = 'Y')
AND RREL.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER' AND grprel.group_member_id = RREL.ROLE_RESOURCE_ID
AND nvl(grprel.delete_flag,'N') = 'N' AND RES.RESOURCE_ID = grprel.RESOURCE_ID
AND res.source_business_grp_id+0 = 0
and groups.group_name not in('华南一区','销售管理部','MDA Team')
and res.source_id in ( 78,98,781,1604,2344 )
) ssg
where rn=1
查询结果如下
1 78 ASIA_ZHANG aa 100000318 华东区 1
3 98 EISLEEPING_LV bb 100000317 华南区 1
