MySQL-FROM 절에서 하위 쿼리를 사용하여 뷰 생성

하위 쿼리를 사용하여 MySQL에서보기를 만들고 싶습니다.

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 절에 하위 쿼리가 있습니다.

이 제한을 어떻게 해결할 수 있습니까?

 

해결 방법

 

하위 쿼리없이 시도하십시오-

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;

 

참조 페이지 https://stackoverflow.com/questions/12797495