Oracle SQL选择问题

我有一个有利益相关者的系统 . 系统可以拥有业务数据所有者,技术数据所有者,两者都可以 . SystemToOwner关系表保存系统的id和利益相关者,并指定利益相关者的角色(Business vs Technical) . 角色实际上是一个驻留在具有各种角色的表中的外键 . 我想从多个表中选择一些信息并将其放入报告中 . 我有一个业务利益相关者专栏和每个系统的技术利益相关者 . 我的问题是,使用当前代码,如果有业务和技术数据所有者,我会得到两行,一行填写业务,技术空白,一行填写技术,业务空白 . 我如何才能将其作为一个条目?

SELECT  system.name "System"
,system.definition "Definition"
,CASE WHEN role.name LIKE 'Business%' THEN nvl2(stk.last_name,stk.last_name || ', ' || stk.first_name, null)ELSE NULL END  "Bus Data Steward"
,CASE WHEN role.name LIKE 'Technical%' THEN nvl2(stk.last_name,stk.last_name || ', ' || stk.first_name, null) ELSE NULL END "Tech Data Steward"
FROM
    system
LEFT JOIN  relationship ON system.element_id = relationship.system
LEFT JOIN  stk ON relationship.stakeholder = stk.element_id
LEFT JOIN  role on relationship.role = role.element_id

回答(1)

2 years ago

您应该使用将角色过滤为其中一个或另一个的子查询,将角色表加入两次:

SELECT * FROM
system
LEFT JOIN  (
  SELECT relationship.system, stk.last_name, stk.firstname, role.name 
  FROM relationship
    INNER JOIN  stk ON relationship.stakeholder = stk.element_id
    INNER JOIN  role on relationship.role = role.element_id WHERE role.name like 'Business%'
) as role_biz 
ON system.element_id = role_biz.system

LEFT JOIN  (
  SELECT relationship.system, stk.last_name, stk.firstname, role.name 
  FROM relationship
    INNER JOIN  stk ON relationship.stakeholder = stk.element_id
    INNER JOIN  role on relationship.role = role.element_id WHERE role.name like 'Technical%'
) as role_tec 
ON system.element_id = role_tec.system