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

Question

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

表扫描占了很大的开销

Answer 1

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