mysql - MySQL使用子查询改善 View

我需要比较同一列中的行，所以我有以下mysql查询，它可以很好地给出预期的结果。

SELECT x.aord,
       x.anode AS parent,
       x.bnode AS child
FROM   (SELECT a.ordinal               AS aord,
               a.id_dt_graph_node_edge AS aid,
               a.id_dt_graph_node      AS anode,
               b.ordinal               AS bord,
               b.id_dt_graph_node_edge AS bid,
               b.id_dt_graph_node      AS bnode
        FROM   dt_graph_node_edge a
               JOIN dt_graph_node_edge b
                 ON a.ordinal < b.ordinal) x
       LEFT JOIN (SELECT a.ordinal               AS aord,
                         a.id_dt_graph_node_edge AS aid,
                         a.id_dt_graph_node      AS anode,
                         b.ordinal               AS bord,
                         b.id_dt_graph_node_edge AS bid,
                         b.id_dt_graph_node      AS bnode
                  FROM   dt_graph_node_edge a
                         JOIN dt_graph_node_edge b
                           ON a.ordinal < b.ordinal) y
              ON x.aord = y.aord
                 AND x.bord > y.bord
WHERE  y.bord IS NULL
ORDER  BY x.aord,
          x.bord

我发现由于错误1349，无法创建此查询的视图。有谁能建议一个更好的方法来做这样的查询，特别关注速度，实际上这个查询非常慢。谢谢。

最佳答案

无法从查询创建VIEW的原因是它包含一个子查询。根据文档，在VIEW查询中不能包含子查询。解决方法是首先在子查询上创建SELECT，例如

CREATE VIEW firstSubquery
AS
SELECT a.ordinal               AS aord,
       a.id_dt_graph_node_edge AS aid,
       a.id_dt_graph_node      AS anode,
       b.ordinal               AS bord,
       b.id_dt_graph_node_edge AS bid,
       b.id_dt_graph_node      AS bnode
FROM   dt_graph_node_edge a
       JOIN dt_graph_node_edge b
         ON a.ordinal < b.ordinal;

CREATE VIEW secondSubquery
AS
SELECT  a.ordinal               AS aord,
        a.id_dt_graph_node_edge AS aid,
        a.id_dt_graph_node      AS anode,
        b.ordinal               AS bord,
        b.id_dt_graph_node_edge AS bid,
        b.id_dt_graph_node      AS bnode
FROM   dt_graph_node_edge a
        JOIN dt_graph_node_edge b
        ON a.ordinal < b.ordinal;

并将新创建的VIEW加入到当前查询中，

CREATE VIEW finalVIEW
AS
SELECT x.aord,
       x.anode AS parent,
       x.bnode AS child
FROM   firstSubquery x
       LEFT JOIN secondSubquery y
              ON x.aord = y.aord AND x.bord > y.bord
WHERE  y.bord IS NULL
ORDER  BY x.aord,  x.bord

PS:MySQL中的视图非常糟糕