我怎样才能在oracle中获得重复的值 [英] How can i get the duplicate value in oracle

问题描述

我已经使用左联接从各个表中选择了以下列，并且得到了以下结果.

Hi I have selected the following columns from various tables using left join and i am getting the following result.

Box_ID    BOX_NAME  VALUE  PROM_EFFDATE       PROM_CLOSEDATE

11      JE 1        115    2/27/2014    
11      JE 2        80     2/27/2014              3/6/2014
11      JE 4        60     2/17/2014    
11      JE 3

如果PROM_CLOSEDATE具有某些值，则应再次显示等效的BOX_NAME(JE2)，其值为null和effdate.我们可以在不同的周期时间内输入任何其他不同的值. 这是实际要求.结果应该是:

If the PROM_CLOSEDATE have some value, then the equivalent BOX_NAME(JE2) should be shown once again with null value and null effdate. We can enter any other different value for different periodic time. This is the actual requirement. the result should be:

Box_ID    BOX_NAME  VALUE  PROM_EFFDATE       PROM_CLOSEDATE

    11      JE 1        115    2/27/2014    
    11      JE 2        80     2/27/2014              3/6/2014
    11      JE 2                          
    11      JE 4        60     2/17/2014    
    11      JE 3

像那样，我必须获得价值.

like that i have to get the value.

根据San的指导，我在这里使用了

Here I have used as per san's guidance,

SELECT Box_ID,BOX_NAME, VALUE, PROM_EFFDATE, PROM_CLOSEDATE
  FROM tab
UNION
SELECT Box_ID,BOX_NAME, NULL, NULL, NULL 
 FROM tab
WHERE PROM_CLOSEDATE IS NOT NULL
order by 2

但是我想要更精确的结果.当我输入JE2(2nd)发生的值和生效日期(不提供CLOSE_DATE)时，那次我得到以下输出.

But i want the result with more precise. when i enter the value and effective date (without giving CLOSE_DATE) for JE2(2nd ) Occurance, that time i am getting the following output.

Box_ID    BOX_NAME  VALUE  PROM_EFFDATE       PROM_CLOSEDATE

        11      JE 1        115    2/27/2014    
        11      JE 2        80     2/27/2014              3/6/2014
        11      JE 2        90     4/6/2014           
        11      JE 2                          
        11      JE 4        60     2/17/2014    
        11      JE 3

但是实际上，当BOX_NAME(此处为JE2)具有没有Closedate的记录时，我不需要具有NULL值，NULL有效日期，NULL闭包的记录. 我只需要在带有closeate的记录为NOT NULL的情况下使用.即使它具有值，生效日期且没有关闭日期的记录，也不应出现空记录. 这是我的要求.请帮助我获得答案.

But actually, i dont need the record with NULL value, NULL effective date, NULL closedate when the BOX_NAME(here JE2) has record with no Closedate. I need only when it has record with closedate is NOT NULL case. Even it has record with value, effective date and no closedate, the null record should not come. This is my requirement. Please Help me to get the answer.

推荐答案

最简单的方法是使用全部联合

The simplest way is by using union all

WITH tab(Box_ID,BOX_NAME, VALUE, PROM_EFFDATE, PROM_CLOSEDATE) AS (
SELECT 11,'JE 1',115,'2/27/2014', NULL FROM dual UNION ALL   
SELECT 11,'JE 2', 80,'2/27/2014','3/6/2014' FROM dual UNION ALL
SELECT 11,'JE 2', 90,'2/28/2014','3/7/2014' FROM dual UNION ALL
SELECT 11,'JE 4', 60,'2/17/2014', NULL  FROM dual UNION ALL    
SELECT 11,'JE 3', NULL, NULL, NULL  FROM dual)
----------
--End of data preparation
----------
SELECT Box_ID,BOX_NAME, VALUE, PROM_EFFDATE, PROM_CLOSEDATE
  FROM tab
UNION
SELECT Box_ID,BOX_NAME, NULL, NULL, NULL 
 FROM tab
WHERE PROM_CLOSEDATE IS NOT NULL
order by 2

输出:

| BOX_ID | BOX_NAME |  VALUE | PROM_EFFDATE | PROM_CLOSEDATE |
|--------|----------|--------|--------------|----------------|
|     11 |     JE 1 |    115 |    2/27/2014 |         (null) |
|     11 |     JE 2 |     80 |    2/27/2014 |       3/6/2014 |
|     11 |     JE 2 | (null) |       (null) |         (null) |
|     11 |     JE 3 | (null) |       (null) |         (null) |
|     11 |     JE 4 |     60 |    2/17/2014 |         (null) |

这篇关于我怎样才能在oracle中获得重复的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！