如何在MySQL中优化此查询?正在从省里取市?TIA
select DISTINCT PM.municipality, count(*)
from anReference as R
inner join anFieldInfo as F on R.ref_id = F.ref_id
inner join tblProvince as PP on F.province_id = PP.province_id
inner join tblMunicipality as PM on F.municipality_id = PM.municipality_id
WHERE R.category = 0 AND R.test = 1
AND PP.region_id=2
AND PM.province_id=7
AND R.date_accessed between '2016-11-01 00:00:00' and '2016-11-30 23:59:00'
GROUP BY PM.municipality;
如果您获得虚高的值,请考虑重写以下内容:
SELECT PM.municipality, count(*)
FROM PM
WHERE tblMunicipality AS PM.province_id=7
AND EXISTS ( ... )
GROUP BY PM.municipality
子查询(中的EXISTS
)是JOINs
所有其他表的子查询。
旁白:这是测试一个月范围的一种“更简单”的方法:
AND R.date_accessed >= '2016-11-01'
AND R.date_accessed < '2016-11-01' + INTERVAL 1 MONTH
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句