ORACLE CASE WHEN 出现多行的情况

[yangzhongzhen]

各位大神，求助一个问题：
两个表：
主表 TAB_MAIN 数据如下
MAIN_ID COL_DE_ASSOR COL_OE_ASSOR

10011 DE_ASSOR1 OE_ASSOR1
10022 DE_ASSOR2 OE_ASSOR2
10033 DE_ASSOR3 OE_ASSOR3
10044 DE_ASSOR4 OE_ASSOR4
有两种 ASSOR DE和 OE 存储在此表中

属性表： TAB_FCAA
FCAA_ID IND_FLAG col_comm
10011 1 comment_111
10011 0 comment_112
10022 1 comment_221
10022 0 comment_222
10033 1 comment_331
10033 0 comment_332

就是当TAB_FCAA.IND_FLAG =1 时认为 是DE 类型的comment，
当 TAB_FCAA.IND_FLAG =0 时认为 是OE 类型的comment，

现在本人这样写的SQL:
select ditstinct
MAIN_ID,
COL_DE_ASSOR,
COL_OE_ASSOR,
case when IND_FLAG=1 then col_comm end as de_com,
case when IND_FLAG=0 then col_comm end as oe_com
from TAB_MAIN
left join TAB_FCAA on TAB_MAIN.MAIN_ID=TAB_FCAA.FCAA_ID

这样一来结果就会出现多行数据：
MAIN_ID COL_DE_ASSOR COL_OE_ASSOR de_com oe_com
10011 DE_ASSOR1 OE_ASSOR1 comment_111
10011 DE_ASSOR1 OE_ASSOR1 comment_112
10022 DE_ASSOR2 OE_ASSOR2 comment_221
10022 DE_ASSOR2 OE_ASSOR2 comment_222
10033 DE_ASSOR3 OE_ASSOR3 comment_331
10033 DE_ASSOR3 OE_ASSOR3 comment_332
10044 DE_ASSOR4 OE_ASSOR4

能否把DE 或者OE 类型的ASSOR 和comment 对应 在一行中 不需要出现上面多行的情况？

不知描述的清楚吗？

因本人现在没法拿到真实数据，印象大致如此， 故求助，谢谢大神们，分不多了，请海涵。

[bw555]

select MAIN_ID,
MAX(COL_DE_ASSOR) AS COL_DE_ASSOR,
MAX(COL_OE_ASSOR) AS COL_OE_ASSOR,
MAX(case when IND_FLAG=1 then col_comm end) as de_com,
MAX(case when IND_FLAG=0 then col_comm end) as oe_com
from TAB_MAIN
left join TAB_FCAA on TAB_MAIN.MAIN_ID=TAB_FCAA.FCAA_ID
GROUP BY TAB_MAIN.MAIN_ID

[bw555]

上面漏写了MAIN_ID的表名，修正下

select TAB_MAIN.MAIN_ID,
MAX(COL_DE_ASSOR) AS COL_DE_ASSOR,
MAX(COL_OE_ASSOR) AS COL_OE_ASSOR,
MAX(case when IND_FLAG=1 then col_comm end) as de_com,
MAX(case when IND_FLAG=0 then col_comm end) as oe_com
from TAB_MAIN
left join TAB_FCAA on TAB_MAIN.MAIN_ID=TAB_FCAA.FCAA_ID
GROUP BY TAB_MAIN.MAIN_ID

[CT_LXL]

引用 楼主 yangzhongzhen 的回复:

select distinct main_id,
                COL_DE_ASSOR,
                COL_OE_ASSOR,
                (select col_comm
                   from TAB_FCAA t2
                  where t1.main_id = t2.fcaa_id
                    and t2.ind_flag = 1) de_com,
                (select col_comm
                   from TAB_FCAA t2
                  where t1.main_id = t2.fcaa_id
                    and t2.ind_flag = 0) oe_com
  from TAB_MAIN t1

[不写代码的钦]

2#

[Tiger_Zhao]

    select distinct
           MAIN_ID,
           COL_DE_ASSOR,
           COL_OE_ASSOR,
            de.col_comm as de_com,
            oe.col_comm as oe_com
      from TAB_MAIN
 left join TAB_FCAA de
        on TAB_MAIN.MAIN_ID = de.FCAA_ID
       AND de.IND_FLAG=1
 left join TAB_FCAA oe
        on TAB_MAIN.MAIN_ID = oe.FCAA_ID
       AND oe.IND_FLAG=0

[yangzhongzhen]

谢谢各位, 明天到正式环境下试一下再结贴。

[Tiger_Zhao]

你 #0 举例数据根本没有反映真实需求。

WITH data(N_CONTROL_KEY,V_CONTROL_DE_ASSESSOR,DE,V_CONTROL_OE_ASSESSOR,OE) AS (
    -- 这里用你 #7 的查询替换
    SELECT 129030633,'SG TNM Creator 1',NULL,'SG TNM Approver 1',   3 UNION ALL
    SELECT 129030633,'SG TNM Creator 1',   3,'SG TNM Approver 1',NULL UNION ALL
    SELECT 129030633,'SG TNM Creator 1',NULL,'SG TNM Approver 1',   2 UNION ALL
    SELECT 129030633,'SG TNM Creator 1',   2,'SG TNM Approver 1',NULL UNION ALL
    SELECT 129030633,'SG TNM Creator 1',NULL,'SG TNM Approver 1',NULL UNION ALL
    SELECT 129030633,'SG TNM Creator 1',NULL,'SG TNM Approver 1',   1 UNION ALL
    SELECT 129030633,'SG TNM Creator 1',   1,'SG TNM Approver 1',NULL
)
,d AS (
    SELECT DISTINCT N_CONTROL_KEY, V_CONTROL_DE_ASSESSOR, DE
      FROM data
)
,o AS (
    SELECT DISTINCT N_CONTROL_KEY, V_CONTROL_OE_ASSESSOR, OE
      FROM data
)
    SELECT d.N_CONTROL_KEY,
           d.V_CONTROL_DE_ASSESSOR,
           d.DE,
           o.V_CONTROL_OE_ASSESSOR,
           o.OE
      FROM d
      JOIN O
        ON d.N_CONTROL_KEY = o.N_CONTROL_KEY
       AND (  (d.DE = o.OE)
            OR(   (d.DE IS NULL)
               AND(o.OE IS NULL)
              )
           )
  ORDER BY d.DE DESC

N_CONTROL_KEY V_CONTROL_DE_ASSESSOR          DE V_CONTROL_OE_ASSESSOR          OE
------------- --------------------- ----------- --------------------- -----------
    129030633 SG TNM Creator 1                3 SG TNM Approver 1               3
    129030633 SG TNM Creator 1                2 SG TNM Approver 1               2
    129030633 SG TNM Creator 1                1 SG TNM Approver 1               1
    129030633 SG TNM Creator 1             NULL SG TNM Approver 1            NULL

[yangzhongzhen]

谢谢大神们的解答，问题研究出来了，再次感谢。

[yangzhongzhen]

今天试了一下：
2楼的方法，虽然能达到目的，但总觉得结果不是很对，少了很多数据，
3楼，5楼数好像不大对。

其实我是想
从

得到


我写的是这样的，得到上面的结果，能得到下面这个结果吗？谢谢 。

SELECT DISTINCT FC.N_CONTROL_KEY,
       FC_DE_UP.V_USR_NAME V_CONTROL_DE_ASSESSOR,
       CASE WHEN FCAA.V_INDICATOR_FLAG=0 THEN FCAA.N_DESIGN_RATING_KEY END AS DE,
       FC_OE_UP.V_USR_NAME V_CONTROL_OE_ASSESSOR,
       CASE WHEN FCAA.V_INDICATOR_FLAG=1 THEN FCAA.N_DESIGN_RATING_KEY END AS OE
FROM DBSATOMIC.FCT_CONTROL FC
LEFT JOIN DBSATOMIC.FCT_CONTROL_ASSESSMENT_AGG FCAA ON FC.N_CONTROL_KEY=FCAA.N_CONTROL_KEY
LEFT JOIN DBSATOMIC.USER_PROFILE FC_DE_UP ON FC.V_CONTROL_DE_ASSESSOR=FC_DE_UP.V_USR_ID
LEFT JOIN DBSATOMIC.USER_PROFILE FC_OE_UP ON FC.V_CONTROL_OE_ASSESSOR=FC_OE_UP.V_USR_ID
WHERE FC.N_CONTROL_KEY='129030633'
ORDER BY FC.N_CONTROL_KEY;

由这个
N_CONTROL_KEY V_CONTROL_DE_ASSESSOR DE V_CONTROL_OE_ASSESSOR OE
129030633 SG TNM Creator1 SG TNM Approver 1 3
129030633 SG TNM Creator1 3 SG TNM Approver 1
129030633 SG TNM Creator1 SG TNM Approver 1 2
129030633 SG TNM Creator 1 2 SG TNM Approver 1
129030633 SG TNM Creator 1 SG TNM Approver 1
129030633 SG TNM Creator 1 SG TNM Approver 1 1
129030633 SG TNM Creator 1 1 SG TNM Approver 1
得到如下
N_CONTROL_KEY V_CONTROL_DE_ASSESSOR DE V_CONTROL_OE_ASSESSOR OE
129030633 SG TNM Creator 1 3 SG TNM Approver 1 3
129030633 SG TNM Creator 1 2 SG TNM Approver 1 2
129030633 SG TNM Creator 1 1 SG TNM Approver 1 1
129030633 SG TNM Creator 1 SG TNM Approver 1


谢谢了啊 。大神们