消除MySQl视图中FROM子句存在的子查询 高手指教！！

Question

mysql视图消除子查询多表结构相同合并数据ERROR 1349 

Answer 1

另外建立1个VIEW　OR　将VIEW保存为表

Answer 2

mysql的bug， 可以先对子查询建立一个View，下一步将你上面的语句里面的子查询改成View去连接

Answer 3

CREATE
    VIEW `view_execution_details` AS SELECT
      (eh.ID+""+d.DEVICE_ID) as id,
        detail.GRID_DATA ,
        d.IP_ADDRESS ,
        eh.JOB_NAME ,
        eh.JOB_GROUP ,
        eh.JOB_TYPE ,
        eh.JOB_CLASS ,
        detail.START_TIME ,
        detail.END_TIME ,
        eh.EXECUTOR ,
        eh.STATUS ,
        detail.ID as detail_id,
        detail.EXECUTION_ID ,
        detail.ERROR ,
        detail.DETAILS
    FROM
        execution_history eh ,
        device d ,
        backup_details detail
    WHERE
        eh.id = detail.EXECUTION_ID
        AND d.device_id = detail.device_id

UNION ALL

SELECT
      (eh.ID+""+d.DEVICE_ID) as id,
        detail.GRID_DATA ,
        d.IP_ADDRESS ,
        eh.JOB_NAME ,
        eh.JOB_GROUP ,
        eh.JOB_TYPE ,
        eh.JOB_CLASS ,
        detail.START_TIME ,
        detail.END_TIME ,
        eh.EXECUTOR ,
        eh.STATUS ,
        detail.ID as detail_id,
        detail.EXECUTION_ID ,
        detail.ERROR ,
        detail.DETAILS
    FROM
        execution_history eh ,
        device d ,
        tool_details detail
    WHERE
        eh.id = detail.EXECUTION_ID
        AND d.device_id = detail.device_id

UNION ALL

SELECT
      (eh.ID+""+d.DEVICE_ID) as id,
        detail.GRID_DATA ,
        d.IP_ADDRESS ,
        eh.JOB_NAME ,
        eh.JOB_GROUP ,
        eh.JOB_TYPE ,
        eh.JOB_CLASS ,
        detail.START_TIME ,
        detail.END_TIME ,
        eh.EXECUTOR ,
        eh.STATUS ,
        detail.ID as detail_id,
        detail.EXECUTION_ID ,
        detail.ERROR ,
        detail.DETAILS
    FROM
        execution_history eh ,
        device d ,
        restore_details detail
    WHERE
        eh.id = detail.EXECUTION_ID
        AND d.device_id = detail.device_id

UNION ALL

SELECT
      (eh.ID+""+d.DEVICE_ID) as id,
        detail.GRID_DATA ,
        d.IP_ADDRESS ,
        eh.JOB_NAME ,
        eh.JOB_GROUP ,
        eh.JOB_TYPE ,
        eh.JOB_CLASS ,
        detail.START_TIME ,
        detail.END_TIME ,
        eh.EXECUTOR ,
        eh.STATUS ,
        detail.ID as detail_id,
        detail.EXECUTION_ID ,
        detail.ERROR ,
        detail.DETAILS
    FROM
        execution_history eh ,
        device d ,
        discovery_details detail
    WHERE
        eh.id = detail.EXECUTION_ID
        AND d.device_id = detail.device_id

Answer 4

版主威武，但是这个SQl会不会太长了？可以优化么？