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

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

  • 果冻剑客 2017-12-05 03:01
    最佳回答

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

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题