Oracle for case inside case查询

Question

我有一个聚合多个表的查询。Table1、Table2、Table3和Table4。查询的运行方式为

SELECT TABLE1.COLUMN1, 
   RESULT.COLUMN1,
   RESULT.COLUMN2, 
   RESULT.COLUMN3,
   RESULT.SOMEAGGCOLUMN, 
   FROM (
            SELECT DISTINCT COLUMN1,COLUMN2,COLUMN3,
                CASE WHEN(SELECT COLUMN1 FROM TABLE2 
                            WHERE TABLE2.ID = TABLE3.TABLE2ID 
                            AND TABLE2.COLUMN2  = 'CRITERIA')
                        THEN (SELECT COLUMN2 FROM TABLE2)
                     ELSE 'DEFAULT VALUE'
                END AS SOMEAGGCOLUMN
            FROM TABLE2, TABLE3
             WHERE TABLE2.ID = TABLE3.TABLE2ID
             GROUPBY
             COLUMN1,
             COLUMN2,
             COLUMN3,
             SOMEAGGCOLUMN
        ) RESULT, TABLE1 WHERE

    RESULT.COLUMN1='CRITERIA'
    AND RESULT.COLUMN2 = 'CRITERIA'
    AND TABLE1.COLUMN1 = 'CRITERIA'
    GROUP BY
    TABLE1.COLUMN1, 
    RESULT.COLUMN1,
    RESULT.COLUMN2, 
    RESULT.COLUMN3,
    RESULT.SOMEAGGCOLUMN

我想以组合的形式聚合和恢复数据。在case select语句中添加AND rownum =1将返回正确的值，但我希望返回不同的多个值，这些值将作为多行返回，并与外部值组合。例如：如果内部查询返回2 TABLE2.COLUMN1 VALUES，我希望有记录表明。

       TABLE1.COLUMN1, 
       RESULT.COLUMN1,
       RESULT.COLUMN2, 
       RESULT.COLUMN3,
       RESULT.SOMEAGGCOLUMN = value one 

       and 

       TABLE1.COLUMN1, 
       RESULT.COLUMN1,
       RESULT.COLUMN2, 
       RESULT.COLUMN3,
       RESULT.SOMEAGGCOLUMN = value two. 

我可以使用查询来实现这一点吗？

  TABLE1
_____________________________
  ID COLUMN1 COLUMN2 COLUMN3
-----------------------------
  1   ABC     T6AD    OTHERM
  2   CDE     T7AD    ANOTHER
----------------------------

  TABLE2 
______________________________
  ID  COLUMN1 COLUMN2  COLUMN3
------------------------------
  1    ASA     T6AD    OTHERM
  2    AFS     T6AD    OTHERM
  3    AED     T7AD    ANOTHER
------------------------------

  TABLE3 
 ________________________________
  ID  TABLE2ID  COLUMN1 COLUMN2
 --------------------------------
  1      1       DETAIL  DETAIL2
  2      2       DETAIL3 DETAIL4
 -------------------------------

我试图实现的是获取表1的结果，然后使用表1的非键列从表2和表3中获取记录。示例结果。

对于来自TABLE1的每个T6AD，我将获得

 ABC T6AD OTHERM ASA DETAIL  DETAIL2
 ABC T6AD OTHERM AFS DETAIL3 DETAIL4

我知道这有点令人困惑，但是想象一下，这三个表具有将它们链接在一起的值，每个表都是一个子集，其中进一步详细说明了存储其他详细信息的结构化数据。我想要遍历到最后一个表，以检索重大表中一条记录的详细信息。单键交叉是不可用的，而是向下遍历。

Answer 1

你看起来只是想：

select t1.column1, t1.column2, t1.column3, t2.column1, t3.column1, t3.column2
from table1 t1
left join table2 t2 on t2.column2 = t1.column2 and t2.column3 = t1.column3
left join table3 t3 on t3.table2id = t2.id
where t1.column2 = 'T6AD';

使用您的样本数据作为CTE：

with table1(id, column1, column2, column3) as (
  select 1, 'ABC', 'T6AD', 'OTHERM' from dual
  union all select 2, 'CDE', 'T7AD', 'ANOTHER' from dual
),
table2 (id, column1, column2, column3) as (
  select 1, 'ASA', 'T6AD', 'OTHERM' from dual
  union all select 2, 'AFS', 'T6AD', 'OTHERM' from dual
  union all select 3, 'AED', 'T7AD', 'ANOTHER' from dual
),
table3 (id, table2id, column1, column2) as (
  select 1, 1, 'DETAIL', 'DETAIL2' from dual
  union all select 2, 2, 'DETAIL3', 'DETAIL4' from dual
)
select t1.column1, t1.column2, t1.column3, t2.column1, t3.column1, t3.column2
from table1 t1
left join table2 t2 on t2.column2 = t1.column2 and t2.column3 = t1.column3
left join table3 t3 on t3.table2id = t2.id
where t1.column2 = 'T6AD';

COL COLU COLUMN3 COL COLUMN1 COLUMN2
--- ---- ------- --- ------- -------
ABC T6AD OTHERM  ASA DETAIL  DETAIL2
ABC T6AD OTHERM  AFS DETAIL3 DETAIL4

如果条件是T7AD，你会得到：

COL COLU COLUMN3 COL COLUMN1 COLUMN2
--- ---- ------- --- ------- -------
CDE T7AD ANOTHER AED                

但仍然不确定您所指的聚合是什么……