MySQL - 在 FROM 子句中使用子查询创建视图 [英] MySQL - create view using subquery in FROM clause

问题描述

我想使用子查询在 MySQL 中创建视图

I want to create view in MySQL using a subquery

CREATE OR REPLACE VIEW `V_TASK_TRANSFER` (`REF_ID`, `DATE_CREATE`, `DATE_TRX`, 
    `ACCOUNT_NO`, `TO_ACCOUNT_NO`, `TO_NAME`, `CURRENCY_CODE`, `AMOUNT`,
    `TASK_TYPE`, `NAME_E`, `NAME_I`, `REF_NO`, `EXECUTION_TYPE`,
    `REVIEW_COUNT`, `REVIEW_NEED`, `APPROVE_COUNT`, `APPROVE_NEED`, 
    `TRX_COUNT_SUCCESS`, `TRX_COUNT_FAIL`, `TRX_COUNT_ALL`,
    `STATUS_TF`, `USER_ID`
) AS 
  SELECT REF_ID,
    DATE_CREATE,
    DATE_TRX,
    ACCOUNT_NO,
    TO_ACCOUNT_NO,
    TO_NAME,
    CURRENCY_CODE,
    AMOUNT,
    TASK_TYPE,
    NAME_E,
    NAME_I,
    REF_NO,
    EXECUTION_TYPE,
    REVIEW_COUNT,
    REVIEW_NEED,
    APPROVE_COUNT,
    APPROVE_NEED,
    TRX_COUNT_SUCCESS,
    TRX_COUNT_FAIL,
    TRX_COUNT_ALL,
    STATUS_TF,
    USER_ID
  FROM
    (SELECT T.REF_ID,
      T.DATE_CREATE,
      T.DATE_TRX,
      TTF.ACCOUNT_NO,
      TTF.TO_ACCOUNT_NO,
      TTF.TO_NAME,
      TTF.CURRENCY_CODE,
      TTF.AMOUNT,
      T.TASK_TYPE,
      TT.NAME_E,
      TT.NAME_I,
      T.REF_NO,
      T.EXECUTION_TYPE,
      T.REVIEW_COUNT,
      T.REVIEW_NEED,
      T.APPROVE_COUNT,
      T.APPROVE_NEED,
      T.TRX_COUNT_SUCCESS,
      T.TRX_COUNT_FAIL,
      T.TRX_COUNT_ALL,
      T.STATUS_TF,
      T.USER_ID
    FROM IB_TASKS T
    JOIN IB_TASK_TRANSFERS TTF ON T.REF_ID = TTF.REF_ID
    JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE
    UNION
    SELECT T.REF_ID,
      T.DATE_CREATE,
      T.DATE_TRX,
      TTF.ACCOUNT_NO,
      TTF.TO_ACCOUNT_NO,
      TTF.TO_NAME,
      TTF.CURRENCY_CODE,
      TTF.AMOUNT,
      T.TASK_TYPE,
      TT.NAME_E,
      TT.NAME_I,
      T.REF_NO,
      T.EXECUTION_TYPE,
      T.REVIEW_COUNT,
      T.REVIEW_NEED,
      T.APPROVE_COUNT,
      T.APPROVE_NEED,
      T.TRX_COUNT_SUCCESS,
      T.TRX_COUNT_FAIL,
      T.TRX_COUNT_ALL,
      T.STATUS_TF,
      T.USER_ID
    FROM IB_TASKS T
    JOIN IB_TASK_TRANSFERS_DOM TTF ON T.REF_ID = TTF.REF_ID
    JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE
    UNION
    SELECT T.REF_ID,
      T.DATE_CREATE,
      T.DATE_TRX,
      TTF.ACCOUNT_NO,
      TTF.TO_ACCOUNT_NO,
      TTF.TO_NAME,
      TTF.CURRENCY_CODE,
      TTF.AMOUNT,
      T.TASK_TYPE,
      TT.NAME_E,
      TT.NAME_I,
      T.REF_NO,
      T.EXECUTION_TYPE,
      T.REVIEW_COUNT,
      T.REVIEW_NEED,
      T.APPROVE_COUNT,
      T.APPROVE_NEED,
      T.TRX_COUNT_SUCCESS,
      T.TRX_COUNT_FAIL,
      T.TRX_COUNT_ALL,
      T.STATUS_TF,
      T.USER_ID
    FROM IB_TASKS T
    JOIN IB_TASK_TRANSFERS_FGN TTF ON T.REF_ID = TTF.REF_ID
    JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE
    );

但它给了我

错误 #1349 - 视图的 SELECT 在 FROM 子句中包含子查询

Error #1349 - View's SELECT contains a subquery in the FROM clause

如何解决这个限制?

推荐答案

尽量不用子查询-

CREATE OR REPLACE VIEW V_TASK_TRANSFER AS
    SELECT T.REF_ID,
      T.DATE_CREATE,
      T.DATE_TRX,
      TTF.ACCOUNT_NO,
      TTF.TO_ACCOUNT_NO,
      TTF.TO_NAME,
      TTF.CURRENCY_CODE,
      TTF.AMOUNT,
      T.TASK_TYPE,
      TT.NAME_E,
      TT.NAME_I,
      T.REF_NO,
      T.EXECUTION_TYPE,
      T.REVIEW_COUNT,
      T.REVIEW_NEED,
      T.APPROVE_COUNT,
      T.APPROVE_NEED,
      T.TRX_COUNT_SUCCESS,
      T.TRX_COUNT_FAIL,
      T.TRX_COUNT_ALL,
      T.STATUS_TF,
      T.USER_ID
    FROM IB_TASKS T
    JOIN IB_TASK_TRANSFERS TTF ON T.REF_ID = TTF.REF_ID
    JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE
    UNION
    SELECT T.REF_ID,
      T.DATE_CREATE,
      T.DATE_TRX,
      TTF.ACCOUNT_NO,
      TTF.TO_ACCOUNT_NO,
      TTF.TO_NAME,
      TTF.CURRENCY_CODE,
      TTF.AMOUNT,
      T.TASK_TYPE,
      TT.NAME_E,
      TT.NAME_I,
      T.REF_NO,
      T.EXECUTION_TYPE,
      T.REVIEW_COUNT,
      T.REVIEW_NEED,
      T.APPROVE_COUNT,
      T.APPROVE_NEED,
      T.TRX_COUNT_SUCCESS,
      T.TRX_COUNT_FAIL,
      T.TRX_COUNT_ALL,
      T.STATUS_TF,
      T.USER_ID
    FROM IB_TASKS T
    JOIN IB_TASK_TRANSFERS_DOM TTF ON T.REF_ID = TTF.REF_ID
    JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE
    UNION
    SELECT T.REF_ID,
      T.DATE_CREATE,
      T.DATE_TRX,
      TTF.ACCOUNT_NO,
      TTF.TO_ACCOUNT_NO,
      TTF.TO_NAME,
      TTF.CURRENCY_CODE,
      TTF.AMOUNT,
      T.TASK_TYPE,
      TT.NAME_E,
      TT.NAME_I,
      T.REF_NO,
      T.EXECUTION_TYPE,
      T.REVIEW_COUNT,
      T.REVIEW_NEED,
      T.APPROVE_COUNT,
      T.APPROVE_NEED,
      T.TRX_COUNT_SUCCESS,
      T.TRX_COUNT_FAIL,
      T.TRX_COUNT_ALL,
      T.STATUS_TF,
      T.USER_ID
    FROM IB_TASKS T
    JOIN IB_TASK_TRANSFERS_FGN TTF ON T.REF_ID = TTF.REF_ID
    JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE;

这篇关于MySQL - 在 FROM 子句中使用子查询创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！