SELECT COUNT(DISTINCT cookieid) cook,
(SELECT COUNT(DISTINCT cookieid) FROM datasources
WHERE DATE_FORMAT(createtime,”%Y-%m-%d”)=DATE_FORMAT(d.createtime,”%Y-%m-%d”)
AND cookieid NOT IN
(SELECT cookieid FROM datasources WHERE DATE_FORMAT(createtime,”%Y-%m-%d”) < DATE_FORMAT(d.createtime,”%Y-%m-%d”))
)ck
FROM datasources d
GROUP BY DATE_FORMAT(d.createtime,”%d”)
(SELECT COUNT(DISTINCT cookieid) FROM datasources
WHERE DATE_FORMAT(createtime,”%Y-%m-%d”)=DATE_FORMAT(d.createtime,”%Y-%m-%d”)
AND cookieid NOT IN
(SELECT cookieid FROM datasources WHERE DATE_FORMAT(createtime,”%Y-%m-%d”) < DATE_FORMAT(d.createtime,”%Y-%m-%d”))
)ck
FROM datasources d
GROUP BY DATE_FORMAT(d.createtime,”%d”)
解决方案
100
这个：
WHERE DATE_FORMAT(createtime,”%Y-%m-%d”) < DATE_FORMAT(d.createtime,”%Y-%m-%d”)
别这么写，不然用不上索引的。
WHERE createtime< DATE_FORMAT(d.createtime,”%Y-%m-%d”)
WHERE DATE_FORMAT(createtime,”%Y-%m-%d”) < DATE_FORMAT(d.createtime,”%Y-%m-%d”)
别这么写，不然用不上索引的。
WHERE createtime< DATE_FORMAT(d.createtime,”%Y-%m-%d”)
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明mysql 语句求优化！
