mysql - 将 View 从 oracle 转换为 mysql

Question

以下是oracle查询，

    CREATE OR REPLACE VIEW view1 AS
 SELECT t1.col1,t1.col2
       FROM (select * FROM table1 asap, table2 pids where asap.parentid= pids.parentid) t1,
            (select * from table1 r_asap,table3 ids where r_asap.id=ids.id) t2
        WHERE
             t1.col2=t2.col2 and
             (t2.col3 <>t1.col3 OR
              t2.col4<>t1.col4 OR
       t2.col5 <>t1.col5 )
       order by t1.col1

我需要将其转换为 MYSQL View ..任何人都可以帮助我

Answer 1

这应该与 mysql 兼容，但总体使用显式连接(对我来说更具可读性)

并且由于 mysql 不允许在 View 中使用子查询，因此您必须首先为子选择创建一个 View ，并在此构建最终 View

    CREATE OR REPLACE VIEW view_sub1 AS
    SELECT col1, col2 
    FROM table1 asap 
    INNER JOIN  table2 pids on asap.parentid= pids.parentid;

    CREATE OR REPLACE VIEW view_sub2 AS
    SELECT c2l2
    FROM table1 r_asap
    INNER JOIN table3 ids on  r_asap.id=ids.id ;

    CREATE OR REPLACE VIEW view1 AS
    SELECT view_sub1.col1,view_sub1.col2
    FROM view_sub1
    INNER JOIN view_sub2 on view_sub1.col2=view_sub2.col2  
    WHERE view_sub2.col3 <>view_sub1.col3 
    OR    view_sub2.col4<>view_sub1.col4
    OR    view_sub2.col5 <>view_sub1.col5;