Oracle多条件判断比对

最新推荐文章于 2025-05-29 09:45:15 发布

原创最新推荐文章于 2025-05-29 09:45:15 发布 · 4.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

Oracle 专栏收录该内容

11 篇文章

订阅专栏

本文提供了一系列SQL查询语句，用于检查车辆识别号码(VIN)的错误、重复及匹配情况，包括长度验证、重复性检查、是否存在以及详细的数据比对。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--VIN错误
select VIN
from DB_INFOMATION i
where length(i.VIN)!=17
;

--VIN重复
select VIN
from DB_INFOMATION i
group by VIN
having count(vin) > 1
;

--不存在的
select DISTINCT VIN
from DB_INFOMATION i
where i.CLXH not in
(
select n.CLXH
from DB_NOTICEPARAM n
)
;

--完全匹配
select DISTINCT VIN
from DB_INFOMATION i, DB_NOTICEPARAM n
where i.CLXH=n.CLXH
and DECODE(i.DCDTXX_XH,null,'N/A',i.DCDTXX_XH)=DECODE(n.DTXH,'NA','N/A',n.DTXH)
and trim(TO_SINGLE_BYTE(DECODE(i.DCDTXX_SCQY,null,'N/A',i.DCDTXX_SCQY)))=trim(TO_SINGLE_BYTE(DECODE(n.DTSCQY,'NA','N/A',n.DTSCQY)))
and DECODE(i.DCZXX_XH,null,'N/A',i.DCZXX_XH)=DECODE(n.CXXH,'NA','N/A',n.CXXH)
and DECODE(i.DCZXX_ZRL,null,'N/A',i.DCZXX_ZRL)=DECODE(n.DCZZRL,'NA','N/A',n.DCZZRL)
and trim(TO_SINGLE_BYTE(DECODE(i.DCZXX_SCQY,null,'N/A',i.DCZXX_SCQY)))=trim(TO_SINGLE_BYTE(DECODE(n.DCZSCQY,'NA','N/A',n.DCZSCQY)))
and DECODE(i.QDDJXX_XH_1,null,'N/A',i.QDDJXX_XH_1)=DECODE(n.QDDJXH,'NA','N/A',n.QDDJXH)
and DECODE(i.QDDJXX_EDGL_1,null,'N/A',i.QDDJXX_EDGL_1)=DECODE(n.QDDJEDGL,'NA','N/A',n.QDDJEDGL)
and trim(TO_SINGLE_BYTE(DECODE(i.QDDJXX_SCQY_1,null,'N/A',i.QDDJXX_SCQY_1)))=trim(TO_SINGLE_BYTE(DECODE(n.QDDJSCQY,'NA','N/A',n.QDDJSCQY)))
and DECODE(i.RLDCXX_XH,null,'N/A',i.RLDCXX_XH)=DECODE(n.RLDCXH,'NA','N/A',n.RLDCXH)
and DECODE(i.RLDCXX_EDGL,null,'N/A',i.RLDCXX_EDGL)=DECODE(n.RLDCEDGL,'NA','N/A',n.RLDCEDGL)
and trim(TO_SINGLE_BYTE(DECODE(i.RLDCXX_SCQY,null,'N/A',i.RLDCXX_SCQY)))=trim(TO_SINGLE_BYTE(DECODE(n.RLDCSCQY,'NA','N/A',n.RLDCSCQY)))
;

--取不匹配最高批
select i.VIN,
NULLIF(DECODE(n.DTXH,'NA','N/A',n.DTXH),DECODE(i.DCDTXX_XH,null,'N/A',i.DCDTXX_XH)) AS DCDTXX_XH,
NULLIF(trim(TO_SINGLE_BYTE(DECODE(n.DTSCQY,'NA','N/A',n.DTSCQY))),trim(TO_SINGLE_BYTE(DECODE(i.DCDTXX_SCQY,null,'N/A',i.DCDTXX_SCQY)))) AS DCDTXX_SCQY,
NULLIF(DECODE(n.CXXH,'NA','N/A',n.CXXH),DECODE(i.DCZXX_XH,null,'N/A',i.DCZXX_XH)) AS DCZXX_XH,
NULLIF(DECODE(n.DCZZRL,'NA','N/A',n.DCZZRL),DECODE(i.DCZXX_ZRL,null,'N/A',i.DCZXX_ZRL)) AS DCZXX_ZRL,
NULLIF(trim(TO_SINGLE_BYTE(DECODE(n.DCZSCQY,'NA','N/A',n.DCZSCQY))),trim(TO_SINGLE_BYTE(DECODE(i.DCZXX_SCQY,null,'N/A',i.DCZXX_SCQY)))) AS DCZXX_SCQY,
NULLIF(DECODE(n.QDDJXH,'NA','N/A',n.QDDJXH),DECODE(i.QDDJXX_XH_1,null,'N/A',i.QDDJXX_XH_1)) AS QDDJXX_XH_1,
NULLIF(DECODE(n.QDDJEDGL,'NA','N/A',n.QDDJEDGL),DECODE(i.QDDJXX_EDGL_1,null,'N/A',i.QDDJXX_EDGL_1)) AS QDDJXX_EDGL_1,
NULLIF(trim(TO_SINGLE_BYTE(DECODE(n.QDDJSCQY,'NA','N/A',n.QDDJSCQY))),trim(TO_SINGLE_BYTE(DECODE(i.QDDJXX_SCQY_1,null,'N/A',i.QDDJXX_SCQY_1)))) AS QDDJXX_SCQY_1,
NULLIF(DECODE(n.RLDCXH,'NA','N/A',n.RLDCXH),DECODE(i.RLDCXX_XH,null,'N/A',i.RLDCXX_XH)) AS RLDCXX_XH,
NULLIF(DECODE(n.RLDCEDGL,'NA','N/A',n.RLDCEDGL),DECODE(i.RLDCXX_EDGL,null,'N/A',i.RLDCXX_EDGL)) AS RLDCXX_EDGL,
NULLIF(trim(TO_SINGLE_BYTE(DECODE(n.RLDCSCQY,'NA','N/A',n.RLDCSCQY))),trim(TO_SINGLE_BYTE(DECODE(i.RLDCXX_SCQY,null,'N/A',i.RLDCXX_SCQY)))) AS RLDCXX_SCQY
from DB_INFOMATION i, (select distinct * from DB_NOTICEPARAM where (clxh,ggpc) in (select clxh,max(GGPC) AS GGPC from DB_NOTICEPARAM group by clxh)) n
where i.CLXH=n.CLXH
;