求大佬帮忙看看sql优化问题

SELECT SUBSTRING(b.AreaCode,
1,
6) AS areaCode,
COUNT(*) AS count
FROM Data_MoJiWeather A JOIN
(SELECT
AreaCode,
Lon,
Lat,
Shi,
Xian,
AreaType
FROM Data_Area_Info
WHERE AreaCode NOT IN
(SELECT AreaCode
FROM Data_Area_Info a
WHERE Xian LIKE '%区'
AND right(left(a.AreaCode,6),2)<06)) B
ON A.location LIKE '%' + b.Shi + b.Xian + '%'
AND b.AreaType = '县'
WHERE CONVERT(varchar(100), up_time, 23) = '2017-11-11'
GROUP BY SUBSTRING(b.AreaCode, 1, 6)
UNION
ALL SELECT SUBSTRING(b.AreaCode,
1,
4)+'01' AS areaCode, COUNT(*) AS count
FROM Data_MoJiWeather A
JOIN
(SELECT AreaCode,
Lon,
Lat,
Shi,
Xian,
AreaType
FROM Data_Area_Info
WHERE AreaCode IN
(SELECT [AreaCode]
FROM Data_Area_Info a
WHERE Xian LIKE '%区'
AND right(left(a.AreaCode,6),2)<06)) B
ON A.location LIKE '%' + b.Shi + b.Xian + '%'
AND b.AreaType = '县'
WHERE CONVERT(varchar(100), up_time, 23) = '2017-11-11'
GROUP BY SUBSTRING(b.AreaCode, 1, 4)+'01'
ORDER BY areaCode


图片说明
表扫描占了很大的开销

1个回答

嵌套那么多子查询,还用in ,还有like 不慢才怪!!!!!!!!!!!,建议in 和子查询 都换成 关联查询,like 的话 只用 like ‘param%’,
这些都是不会用索引的操作,还用就是看看你使用的函数,有哪一些是不会使用索引的,建议找替代品

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!