wang_chen_Wcc
我要学习学习使我快乐
采纳率33.3%
2017-12-05 01:23

求大佬帮忙看看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条回答

  • ios_king 果冻剑客 4年前

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

    点赞 1 评论 复制链接分享

相关推荐