将 oracle 查询转换为 MySQL 查询 [英] Converting an oracle query to MySQL query

问题描述

我正在尝试将此查询从 Oracle 转换为 MySQL:

I'm trying to convert this query from Oracle to MySQL:

CREATE TABLE SCT_201301_UMLS_SYN 
AS SELECT DISTINCT A.SCUI "SNOMED_CODE", A.STR "SNOMED_DISPLAY_NAME"
   , B.STR "UMLS_SYNONYM" 
FROM MRCONSO A, MRCONSO_UMLS B 
WHERE A.SCUI IN (SELECT referencedComponentId 
                 FROM SnomedCTtoICD10) 
AND A.SAB='SNOMEDCT' 
AND A.SUPPRESS='N' 
AND A.TTY='PT' 
AND B.LAT='ENG' 
AND B.SUPPRESS='N' 
AND A.CUI=B.CUI

任何想法如何做到这一点?

Any ideas how to do this?

推荐答案

如果您使用的是旧版本的 MySQL，那么您可能会遇到 in 的性能问题.这是一个替代方案，它应该适用于 MySQL 和 Oracle:

If you are using an older version of MySQL, then you might have a problem with the performance of in. Here is an alternative, which should work in both MySQL and Oracle:

CREATE TABLE SCT_201301_UMLS_SYN 
AS SELECT DISTINCT A.SCUI "SNOMED_CODE", A.STR "SNOMED_DISPLAY_NAME"
   , B.STR "UMLS_SYNONYM" 
FROM MRCONSO A join
     MRCONSO_UMLS B
     on A.CUI = B.CUI
WHERE exists (SELECT 1
              FROM SnomedCTtoICD10 s
              where s.referencedComponentId  = a.scui) 
AND A.SAB='SNOMEDCT' 
AND A.SUPPRESS='N' 
AND A.TTY='PT' 
AND B.LAT='ENG' 
AND B.SUPPRESS='N' 

这篇关于将 oracle 查询转换为 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！