mysql 서브쿼리에서 outer column을 액세스하는 방법은 무엇입니까?

mysql 서브쿼리에서 outer column을 액세스하는 방법은 무엇입니까?

질문이 있습니다.

SELECT t1.account AS main, t1.id, tmp.* FROM account.account t1 LEFT JOIN
        (
        SELECT
            tmp1.account,
            tmp1.lot - tmp2.lot AS nett
        FROM
            ( SELECT account, SUM( lot ) AS lot
            FROM orders WHERE market_date = "2020-07-20"
            AND `transaction` = 1 AND account = t1.id ) AS
            tmp1 
            LEFT JOIN ( SELECT account, SUM( lot ) AS lot
            FROM orders WHERE market_date = "2020-07-20"
            AND `transaction` = 2 AND account = t1.id ) AS
            tmp2 ON tmp1.account = tmp2.account 
            ) tmp ON tmp.account = t1.id;

그 결과

1054 - Unknown column 't1.id' in 'where clause', Time: 0.001000s

t1.id에 접속하는 방법 현재 서브쿼리 선택?

주문 시 단일 수준 하위 쿼리에 대한 참여만 사용해 보십시오.

SELECT
    a.account AS main,
    a.id,
    COALESCE(o.nett, 0) AS nett
FROM account a
LEFT JOIN
(
    SELECT
        account,
        SUM(CASE WHEN `transaction` = 1 THEN lot END) -
            SUM(CASE WHEN `transaction` = 2 THEN lot END) AS nett
    FROM orders
    WHERE market_date = '2020-07-20'
    GROUP BY account
) o
    ON o.account = a.account;

언급URL : https://stackoverflow.com/questions/63126965/how-to-acces-outer-column-in-mysql-subquery