从同一表中选择具有不同WHERE条件的两列 [英] Select two columns from same table with different WHERE conditions

问题描述

我有两个选择语句，我想生成两列，每个列并排使用一个选择语句中的这两个选择语句

I have two select statements and I want to generate two columns, one from each statement side by side using these two select statements inside a single select statement

查询1

 SELECT DISTINCT CASE_ID 
 from t1 
 WHERE MODIFIED_DATE BETWEEN TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-56 
                       AND TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-49 

CASE_ID
12
13
14
15
17

查询2

SELECT DISTINCT CASE_ID
from t1 
WHERE MODIFIED_DATE BETWEEN TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-49 
                      AND TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-42

CASE_ID
45
98
67
90
76
82
61

最终输出应类似于:

C1   C2
12   45
13   98
14   67
15   90
17   76
     82
     61

有人可以告诉我该怎么做吗?

Could anyone tell me how to do to so?

谢谢.

更新

我从答案中尝试过的查询之一:

One of the query I tried from the answers :

SELECT DISTINCT 
             case when 
                MODIFIED_DATE BETWEEN TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD') - 56 
                                  AND TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD') - 49
               then CASE_ID 
             end as c1,
        DISTINCT 
               case when 
                  MODIFIED_DATE BETWEEN TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-49  
                                    AND TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-42 
                  then CASE_ID 
               end as c2
from t1 
WHERE MODIFIED_DATE 
         BETWEEN TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD') - 56 
             AND TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD') - 42

我得到ORA-00936:缺少表达.谁能告诉我问题所在?

And I am getting ORA-00936: missing expression. Could anyone tell me the problem?

谢谢.

推荐答案

您正在寻找经过过滤的聚合:

You are looking for a filtered aggregate:

SELECT COUNT(DISTINCT 
             case when 
                MODIFIED_DATE BETWEEN TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD') - 56 
                                  AND TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD') - 49
               then CASE_ID 
             end) as c1,
        COUNT(DISTINCT 
               case when 
                  MODIFIED_DATE BETWEEN TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-49  
                                    AND TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-42 
                  then CASE_ID 
               end) as c2
from t1 
WHERE MODIFIED_DATE 
         BETWEEN TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD') - 56 
             AND TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD') - 42 

请注意，WHERE子句现在需要覆盖两个间隔.

Note that the WHERE clause now needs to cover both intervals.

这篇关于从同一表中选择具有不同WHERE条件的两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！