mysql 嵌套问题

wenkekenihao 2016-04-11 02:20:41

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高手指导指导，应该怎么写才行。
谢谢!

...全文

130 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

阳泉酒家小当家 2016-04-12

打赏
举报

我自己写了一个简单的语句，模仿你的语句，并没有报错，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")

阳泉酒家小当家 2016-04-12

打赏
举报

没有报错

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)

benluobo 2016-04-11

打赏
举报

引用 2 楼 wenkekenihao 的回复:

不行啊。。。。。。。。。。。。。。。

报什么错误

ACMAIN_CHM 2016-04-11

打赏
举报

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")

wenkekenihao 2016-04-11