mysql - 将Oracle转换为MySQL:看到不同的输出

我有一个来自Oracle的SQL查询，我正试图将其转换为MySQL。
下面是两个查询的一部分。数据库结构不同，因此您会发现表和列的名称不同。
我在MYSQL部分运行时没有得到任何结果，需要帮助找出原因吗？
Oracle SQL查询：

    (SELECT  all_ips.child_itm_num,
             pres.child_itm_num AS present,
             prev.child_itm_num AS potential_prev_version
     FROM
            (SELECT DISTINCT child_itm_num
             FROM    EDW.dpdm_bom bom
             WHERE   root_itm_num IN ('a','b')) all_ips,
            (SELECT DISTINCT child_itm_num
             FROM    EDW.dpdm_bom bom
             WHERE   root_itm_num = 'b') pres,
   (SELECT DISTINCT child_itm_num
    FROM    EDW.dpdm_bom bom
    WHERE   root_itm_num = 'a') prev
     WHERE  SUBSTR(all_ips.child_itm_num,0,LENGTH(all_ips.child_itm_num) - 3) = SUBSTR(pres.child_itm_num, 0,LENGTH(pres.child_itm_num) - 3)
     AND    SUBSTR(prev.child_itm_num,0,LENGTH(prev.child_itm_num) - 3)       = SUBSTR(pres.child_itm_num, 0,LENGTH(pres.child_itm_num) - 3)
     AND    prev.child_itm_num NOT IN (SELECT child_itm_num
                                       FROM    EDW.dpdm_bom bom
                                       WHERE   root_itm_num ='b'))

MySQL查询：

    (SELECT  all_ips.child_name,
             pres.child_name AS present,
             prev.child_name AS potential_prev_version
     FROM
            (SELECT DISTINCT child_name
             FROM    DPDM_BOM bom
             WHERE   child_name IN ('a','b')) all_ips,
            (SELECT DISTINCT child_name
             FROM    DPDM_BOM bom
             WHERE   revision_name = 'b') pres,
            (SELECT DISTINCT child_name
             FROM    DPDM_BOM bom
             WHERE   revision_name = 'a') prev
  WHERE  SUBSTR(all_ips.child_name,0,LENGTH(all_ips.child_name) - 3) = SUBSTR(pres.child_name, 0,LENGTH(pres.child_name) - 3)
  AND    SUBSTR(prev.child_name,0,LENGTH(prev.child_name) - 3)       = SUBSTR(pres.child_name, 0,LENGTH(pres.child_name) - 3)
  AND    prev.child_name NOT IN (SELECT child_name
                                       FROM    DPDM_BOM bom
                                       WHERE   revision_name = 'b'))

请帮忙！
阿努沙。

最佳答案

我认为你应该用WHERE child_name IN ('a','b')代替WHERE revision_name IN ('a','b')。另外，使用CHAR_LENGTH而不是LENGTH（尽管Mysql也支持LENTGH）。

(SELECT  all_ips.child_name,
             pres.child_name AS present,
             prev.child_name AS potential_prev_version
     FROM
            (SELECT DISTINCT child_name
             FROM    DPDM_BOM bom
             WHERE   revision_name IN ('a','b')) all_ips,
            (SELECT DISTINCT child_name
             FROM    DPDM_BOM bom
             WHERE   revision_name = 'b') pres,
            (SELECT DISTINCT child_name
             FROM    DPDM_BOM bom
             WHERE   revision_name = 'a') prev
  WHERE  SUBSTR(all_ips.child_name,0,CHAR_LENGTH(all_ips.child_name) - 3) = SUBSTR(pres.child_name, 0,CHAR_LENGTH(pres.child_name) - 3)
  AND    SUBSTR(prev.child_name,0,LENGTH(prev.child_name) - 3)       = SUBSTR(pres.child_name, 0,LENGTH(pres.child_name) - 3)
  AND    prev.child_name NOT IN (SELECT child_name
                                       FROM    DPDM_BOM bom
                                       WHERE   revision_name = 'b'))

希望有帮助！

关于mysql - 将Oracle转换为MySQL:看到不同的输出，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47560961/