mysql多表联查，子查询问题

Question

需求：统计表只有“cre_date”字段有值，现在需要根据“cre_date”字段从另外四张表的中计算总值，并赋值到统计表“transactions”字段中

问题：我写的sql不知道是因为子查询太深了获取不到父查询的结果，还是因为其他原因，请教一下大家该如何修改

有五张表：

t_summary_yesterday --统计表
|--表内字段：id --主键递增，cre_date --创建时间，transactions --交易总额

t_data_a_transaction_deal --a类型交易处理表
|--表内字段：id --主键递增，cre_date --创建时间，trans_amount--交易额
t_data_a_transaction --a类型交易表
|--表内字段：id --主键递增，cre_date --创建时间，trans_amount--交易额

t_data_b_transaction_deal --b类交易处理表
|--id --主键递增，cre_date --创建时间，transactions --交易额
t_data_b_transaction --b类交易表
|--id --主键递增，cre_date --创建时间，transactions --交易额

以下是我写的sql

SELECT
    z.cre_date,
    (
    SELECT
        sum( transactions )
    FROM
        (
        SELECT TRUNCATE
            ( COALESCE ( sum( s.transactions ), 0 ), 2 ) AS transactions 
        FROM
            (
            SELECT TRUNCATE
                ( COALESCE ( sum( t.trans_amount ), 0 ), 2 ) AS transactions 
            FROM
                t_data_a_transaction_deal t 
            WHERE
                t.cre_date = z.cre_date  UNION ALL
            SELECT TRUNCATE
                ( COALESCE ( sum( t.trans_amount ), 0 ), 2 ) AS transactions 
            FROM
                t_data_b_transaction_deal t 
            WHERE
                t.cre_date = z.cre_date 
            ) AS s 
      UNION ALL
        SELECT TRUNCATE
            ( COALESCE ( sum( s.transactions ), 0 ), 2 ) AS transactions 
        FROM
            (
            SELECT TRUNCATE
                ( COALESCE ( sum( t.trans_amount ), 0 ), 2 ) AS transactions 
            FROM
                t_data_a_transaction t 
            WHERE
                t.cre_date = z.cre_date UNION ALL
            SELECT TRUNCATE
                ( COALESCE ( sum( t.trans_amount ), 0 ), 2 ) AS transactions 
            FROM
                t_data_b_transaction t 
            WHERE
                t.cre_date = z.cre_date 
            ) AS s 
        ) AS c 
    ) AS 'transactions' 
FROM
    t_summary_yesterday z 

sql执行后的结果

1054 - Unknown column 'z.cre_date' in 'where clause'

Answer 1

1、子查询中的z是感知不到主查询，每个子查询都是独立的
2、写法可以如下

```sql
update t_summary_yesterday a,
    (select cre_date,sum(transactions) transactions from (
        select  cre_date,sum(trans_amount) transactions from t_data_a_transaction_deal group by 1
        union all 
        select  cre_date,sum(trans_amount) transactions from t_data_a_transaction group by 1
        union all 
        select  cre_date,sum(transactions) transactions from t_data_b_transaction_deal group by 1
        union all 
        select  cre_date,sum(transactions) transactions from t_data_b_transaction group by 1
        ) tmp_a group by 1
    ) b 
set a.transactions=b.transactions
where a.cre_date=b.cre_date

```