请问下面这语句该怎么优化SELECT a.id,a.mobile,a.password,a.telecom,a.cityId,a.provinceId,a.gprsSwitch,
a.gprsPackage,a.monternetSwitch,a.imsiNo,a.puk,a.receipt,a.channelStatus
,a.buyTime , a.res ,a.price,b.holder,b.balance,b.status
,b.createTime FROM SIM_MANAGER a , SIM_LOG b WHERE a.id=b.simId
AND b.createTime IN (SELECT MAX(createTime) FROM SIM_LOG GROUP
BY simId)
a.gprsPackage,a.monternetSwitch,a.imsiNo,a.puk,a.receipt,a.channelStatus
,a.buyTime , a.res ,a.price,b.holder,b.balance,b.status
,b.createTime FROM SIM_MANAGER a , SIM_LOG b WHERE a.id=b.simId
AND b.createTime IN (SELECT MAX(createTime) FROM SIM_LOG GROUP
BY simId)
你倒是可以把所以字段换成大写,因为数据库里面是大写的,这样可能快点(纯学校的理论知识)
BY simId)MAX就已经唯一了,不要用IN了,直接用等于号吧
哥哥 别人按simId 分组的,有多少个simId 就有多少个max
a.gprsPackage,a.monternetSwitch,a.imsiNo,a.puk,a.receipt,a.channelStatus
,a.buyTime , a.res ,a.price,b.holder,b.balance,b.status
,b.createTime 这么多字段 看看有没有 不需要的,只查询必要的字段可以提高效率
SELECT a.id,a.mobile,a.password,a.telecom,a.cityId,a.provinceId,a.gprsSwitch,
a.gprsPackage,a.monternetSwitch,a.imsiNo,a.puk,a.receipt,a.channelStatus
,a.buyTime , a.res ,a.price,b.holder,b.balance,b.status
,b.createTime
FROM SIM_MANAGER a , SIM_LOG b ,(SELECT MAX(createTime) createTime FROM SIM_LOG GROUP BY simId) c
WHERE a.id=b.simId
AND b.createTime = c.createTime
a.gprsPackage,a.monternetSwitch,a.imsiNo,a.puk,a.receipt,a.channelStatus
,a.buyTime , a.res ,a.price,b.holder,b.balance,b.status
,b.createTime
FROM SIM_MANAGER a inner join SIM_LOG b on a.id=b.simId
inner join (SELECT MAX(createTime) createTime FROM SIM_LOG GROUP BY simId) c
on b.createTime = c.createTime--字段 看看有没有 不需要的,只查询必要的字段可以提高效率