如果在表中发现重复数据(而不是重复记录)，则抛出错误

qlzsbp2j 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(214)

我有一个表，其中包含有关产品的信息，我需要检查重复的记录，并抛出一个错误。有两列，product key和product value，其中product value包含分号分隔值和正常值。样本数据如下(（无约束）

source_id |Product_key | Product_value
-----------------------------------------------------------
     1      xzy         PRODUCT_TAG=SCENT;CODE=123;PRICE=234
     1      xhmr          POWDER
     1      abc           PRODUCT_TAG=COMB;CODE=123;PRICE=234
     1      xhmr          OIL
     1      zrmt          123

现在我要检查任意两行是否有相同的product\u key和product\u tag值，如果product\u key是xhmr，那么product\u value应该被视为product\u tag值。下面是我写的问题

select source_id, PRODUCT_KEY, rec , (case when  instr(rec,'PRODUCT_TAG')<>0 THEN regexp_substr(TRIM(rec), '[^=]+', 1,2)
ELSE rec
end) as PRODUCT_TAG
from 
(select source_id,PRODUCT_KEY ,regexp_substr(TRIM(PRODUCT_VALUE), '[^;]+', 1,LEVEL) AS rec from products 
 connect by  regexp_substr(TRIM(PRODUCT_VALUE), '[^;]+', 1,LEVEL) is not null
    AND prior source_id = source_id
   AND PRIOR SYS_GUID() IS NOT NULL) where instr(rec,'PRODUCT_TAG')<>0 or PRODUCT_KEY in('xhmr');

输出

source_id |Product_key | Product_value
-----------------------------------------------------------
     1      xzy           SCENT
     1      xhmr          POWDER
     1      abc           COMB
     1      xhmr          OIL

在这之后，我将对所有行和所有不同的行进行计数。如果两个计数不相等，则抛出错误。我想知道，是否所有这些都可以用一种简洁的方式来完成。

sql oracle oracle11g

来源：https://stackoverflow.com/questions/63068450/throw-error-if-duplicate-data-not-duplicate-records-found-in-a-table

1条答案

按热度按时间

qgelzfjb1#

我想你可以做以下几点
获取主查询并放入with子句
然后你可以根据需要多次操作它
你可以这样写：
更新

with main_query 
    as ( 
    select source_id, PRODUCT_KEY, rec , (case when  instr(rec,'PRODUCT_TAG')<>0 THEN regexp_substr(TRIM(rec), '[^=]+', 1,2)
    ELSE rec
    end) as PRODUCT_TAG
    from 
    (select source_id,PRODUCT_KEY ,regexp_substr(TRIM(PRODUCT_VALUE), '[^;]+', 1,LEVEL) AS rec from products 
     connect by  regexp_substr(TRIM(PRODUCT_VALUE), '[^;]+', 1,LEVEL) is not null
        AND prior source_id = source_id
       AND PRIOR SYS_GUID() IS NOT NULL) where instr(rec,'PRODUCT_TAG')<>0 or PRODUCT_KEY in('xhmr')
    ) 
    select 
    case when total_value = tot_dist then 'OK' -- whatever you want here
    else 'ERROR' -- whatever you want here
    end as result
    from 
    (  ( select count(*) as total_value  from main_query ) ,
       ( select count(*) as total_dist  from ( select distinct * from main_query ) ) 
    )

我的例子

SQL> desc my_test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 C2                                                 NUMBER

SQL> select * from my_test ;

        C1         C2
---------- ----------
         1          1
         1          1
         2          2

SQL>  with main_query as ( select c1 , c2 from my_test )
  select a , b
 from
 ( select count(*) as a  from main_query ) ,
 ( select count(*) as b  from ( select distinct * from main_query ) )
 /  2    3    4    5    6

         A          B
---------- ----------
         3          2

 SQL>  with main_query as ( select c1 , c2 from my_test )
  select case when a = b then 'OK' else 'ERROR' end as result
 from
 ( select count(*) as a  from main_query ) ,
 ( select count(*) as b  from ( select distinct * from main_query ) )
/

RESUL
-----
ERROR

赞(0）回复(0）举报 2021-07-24

我来回答

如果在表中发现重复数据(而不是重复记录)，则抛出错误

1条答案

相关问题

热门标签

最新问答