mysql 嵌套问题

Question

select DATE_FORMAT(addTime,"%Y-%m-%d") as date,
(select count(*) from (select mac from accessusers where DATE_FORMAT(addTime,"%Y-%m-%d")=DATE_FORMAT(abs.addTime,"%Y-%m-%d") group by mac) aa) as userCount,
(select count(*) from (select mac from newaccessusers where DATE_FORMAT(addTime,"%Y-%m-%d")=DATE_FORMAT(abs.addTime,"%Y-%m-%d") group by mac) aa) as newuserCount from accessusers abs where 1=1  group by DATE_FORMAT(addTime,"%Y-%m-%d")以上语句运行提示：[Err] 1054 - Unknown column 'abs.addTime' in 'where clause'为什么不能在子句中引用 主句的别名.望各位sql高手指导指导，应该怎么写才行。
谢谢!

Answer 1

直接改group by DATE_FORMAT(addTime,"%Y-%m-%d") 为  group by `date`

Answer 2

select DATE_FORMAT(addTime,"%Y-%m-%d") as date,
(select count(distinct mac) from  accessusers 
where DATE_FORMAT(addTime,"%Y-%m-%d")=DATE_FORMAT(abs.addTime,"%Y-%m-%d") 
) as userCount,
(select count(distinct mac) from newaccessusers 
where DATE_FORMAT(addTime,"%Y-%m-%d")=DATE_FORMAT(abs.addTime,"%Y-%m-%d")  
) as newuserCount from accessusers abs where 1=1  group by DATE_FORMAT(addTime,"%Y-%m-%d")
 
 

Answer 3

我自己写了一个简单的语句，模仿你的语句，并没有报错，abs虽然是函数名，但是并不是关键字，所以照理也不应该报错才对。格式化后的代码如下：
SELECT  DATE_FORMAT(addTime, "%Y-%m-%d") AS date ,
        ( SELECT    COUNT(*)
          FROM      ( SELECT    mac
                      FROM      accessusers
                      WHERE     DATE_FORMAT(addTime, "%Y-%m-%d") = DATE_FORMAT(abs.addTime,
                                                              "%Y-%m-%d")
                      GROUP BY  mac
                    ) aa
        ) AS userCount ,
        ( SELECT    COUNT(*)
          FROM      ( SELECT    mac
                      FROM      newaccessusers
                      WHERE     DATE_FORMAT(addTime, "%Y-%m-%d") = DATE_FORMAT(abs.addTime,
                                                              "%Y-%m-%d")
                      GROUP BY  mac
                    ) aa
        ) AS newuserCount
FROM    accessusers ABS
WHERE   1 = 1
GROUP BY DATE_FORMAT(addTime, "%Y-%m-%d")

Answer 4

没有报错mysql> select ABS.ID, (select count(*) from test where id = abs.id) from test ABS GROUP BY ABS.ID;
+----+-----------------------------------------------+
| ID | (select count(*) from test where id = abs.id) |
+----+-----------------------------------------------+
|  3 |                                             1 |
|  6 |                                             1 |
| 11 |                                             1 |
+----+-----------------------------------------------+
3 rows in set (0.05 sec)