mysql子查询的子查询无法使用外层参数问题

问题遇到的现象和发生背景

系统用户表 sys_user 员工表 t_employee_base_info 客户表 t_cust_base_info 员工客户聊天记录表 t_chat_record
员工数据统计表 t_statistic_employee_info
一个系统用户下有多个员工，现需以系统用户为维度进行数据统计，用下面的sql报Unknown column 't.user_id' in 'where clause'
INTERACTIVE_CUST_NUM ：给员工发送过消息的客户数量（去重的）

SELECT 
t.user_id,
(SELECT COUNT(1) FROM(
      SELECT 1 from t_cust_base_info a
      LEFT JOIN t_chat_record b on a.CUST_ID = b.MSG_FROM
    LEFT JOIN t_employee_base_info c on b.MSG_RECEIVE = c.EMPLOYEE_ID
      WHERE c.SYS_USER_ID = t.user_id
      AND date_format(b.MSG_TIME,'%Y-%m-%d') >= '2021-12-01'
      AND date_format(b.MSG_TIME,'%Y-%m-%d')<= '2021-12-21'
      GROUP BY a.CUST_ID) d) as INTERACTIVE_CUST_NUM,
sum(t2.AVG_REPLY_TIME),
sum(t2.REPLY_PERCENTAGE)
FROM sys_user t
LEFT JOIN t_employee_base_info t1 on t.user_id = t1.SYS_USER_ID
LEFT JOIN t_statistic_employee_info t2 on t1.EMPLOYEE_ID = t2.EMPLOYEE_ID
where t.user_id in( '100','103')
AND t2.STAT_TIME >= '2021-12-01'
AND t2.STAT_TIME <= '2021-12-21'
GROUP BY t.user_id

我的解答思路和尝试过的方法

用UNION ALL也可以实现，想看下有没有其他方法

```sql
SELECT t1.user_id,
SUM(t1.INTERACTIVE_CUST_NUM) as INTERACTIVE_CUST_NUM,
SUM(t1.AVG_REPLY_TIME) as AVG_REPLY_TIME,
SUM(t1.REPLY_PERCENTAGE) as REPLY_PERCENTAGE
from 
(SELECT 
t.user_id,
0 as INTERACTIVE_CUST_NUM,
sum(t2.AVG_REPLY_TIME) as AVG_REPLY_TIME,
sum(t2.REPLY_PERCENTAGE) as REPLY_PERCENTAGE
FROM sys_user t
LEFT JOIN t_employee_base_info t1 on t.user_id = t1.SYS_USER_ID
LEFT JOIN t_statistic_employee_info t2 on t1.EMPLOYEE_ID = t2.EMPLOYEE_ID
where t.user_id in( '100','103')
AND t2.STAT_TIME >= '2021-12-01'
AND t2.STAT_TIME <= '2021-12-21'
GROUP BY t.user_id
UNION ALL 
SELECT t.SYS_USER_ID as user_id,COUNT(*) as INTERACTIVE_CUST_NUM,0 as AVG_REPLY_TIME,0 as REPLY_PERCENTAGE FROM
(SELECT c.SYS_USER_ID,a.CUST_ID from t_cust_base_info a
LEFT JOIN t_chat_record b on a.CUST_ID = b.MSG_FROM
LEFT JOIN t_employee_base_info c on b.MSG_RECEIVE = c.EMPLOYEE_ID
WHERE c.SYS_USER_ID in( '100','103')
AND date_format(b.MSG_TIME,'%Y-%m-%d') >= '2021-12-01'
AND date_format(b.MSG_TIME,'%Y-%m-%d')<= '2021-12-21'
GROUP BY c.SYS_USER_ID,a.CUST_ID) t
GROUP BY t.SYS_USER_ID) t1
GROUP BY t1.user_id

```

你套了两层括号,它认不到了,删掉一层即可

SELECT 
t.user_id,
(SELECT COUNT(1) from t_cust_base_info a
      LEFT JOIN t_chat_record b on a.CUST_ID = b.MSG_FROM
    LEFT JOIN t_employee_base_info c on b.MSG_RECEIVE = c.EMPLOYEE_ID
      WHERE c.SYS_USER_ID = t.user_id
      AND date_format(b.MSG_TIME,'%Y-%m-%d') >= '2021-12-01'
      AND date_format(b.MSG_TIME,'%Y-%m-%d')<= '2021-12-21'
      GROUP BY a.CUST_ID)  as INTERACTIVE_CUST_NUM,
sum(t2.AVG_REPLY_TIME),
sum(t2.REPLY_PERCENTAGE)
FROM sys_user t
LEFT JOIN t_employee_base_info t1 on t.user_id = t1.SYS_USER_ID
LEFT JOIN t_statistic_employee_info t2 on t1.EMPLOYEE_ID = t2.EMPLOYEE_ID
where t.user_id in( '100','103')
AND t2.STAT_TIME >= '2021-12-01'
AND t2.STAT_TIME <= '2021-12-21'
GROUP BY t.user_id

注:以上sql只针对报错问题解决,不表示认可此sql逻辑,也未进行优化