mysql简单统计

SELECT * FROM(
SELECT
p.channels
,DATE_FORMAT(v.creattime,'%Y-%m-%d') dt
,COUNT(
DISTINCT CASE WHEN DATE_FORMAT(v.creattime,'%Y%m%d') = DATE_FORMAT(p.addTime,'%Y%m%d') THEN p.deviceId END 
) newUserUVCount -- new


,SUM(
CASE WHEN DATE_FORMAT(v.creattime,'%Y%m%d') = DATE_FORMAT(p.addTime,'%Y%m%d') THEN 1 ELSE 0 END
) newcountPV -- new

,COUNT(
DISTINCT CASE WHEN DATE_FORMAT(v.creattime,'%Y%m%d') > DATE_FORMAT(p.addTime,'%Y%m%d') THEN p.deviceId END 
) oldUserUVCount -- old

FROM visit_logging v LEFT JOIN t_phoneuser p ON v.phoneuser_id=p.id 
WHERE 1=1 
AND NOT ISNULL(p.channels)
AND v.dt>='20150917' AND v.dt<='20150923'
GROUP BY DATE_FORMAT(v.creattime,'%Y-%m-%d') , p.channels

) t ORDER BY t.dt DESC

;