sql语句优化小结
2024-10-20 16:08:16
sql的优化技巧。
1.用join进行子查询的优化。
低效的子查询
select a.user_name,a.over,(select over from user2 b where a.user_name=b.user_name) as over2 from user1 a;
使用join优化后的子查询:
select a.user_name,a.over,b.over as over2 from user1 a left join user2 b on a.user_name=b.user_name;
2.用join进行聚合函数的优化。
使用join + having优化聚合子查询:
select a.user_name,b.timestr,b.kills from user1 a
join user_kills b on a.id = b.user_id
join user_kills c on c.user_id = b.user_id
group by a.user_name,btimestr,b.kills
having b.kills = max(c.kills);
3.分类聚合函数的优化
分类聚合方式查询每一个用户某一个字段数据最大的两条数据:
select d.user_name ,c.ctimestr,kills from
(select user_id ,timestr ,kills ,(
select count(*) from user_kills b where b.user_id = a.user_id and a.kills <= b.kills) as cnt
from user_kills a group by user_id,timestr,kills) c
join user1 d on c.user_id = d.id where cnt <= 2
4.自连接行转列
select * from (
select sum(kills) as '孙悟空' from user1 a
join user_kills b
on a.id = b.use_id and a.user_name='孙悟空'
) a cross join (
select sum(kills) as '猪八戒' from user1 a
join user_kills b
on a.id = b.use_id and a.user_name='猪八戒'
) b cross join(
select sum(kills) as '沙僧' from user1 a
join user_kills b
on a.id = b.use_id and a.user_name='沙僧'
) c
5.case语句列转行
select
sum(case when user_name=' 孙悟空' then kills end) as '孙悟空',
sum(case when user_name='猪八戒' then kills end) as '猪八戒',
sum(case when user_name='沙僧' then kills end) as '沙僧'
from user1 a join use_kills b
on a.id = b.user_id;
6.使用序列化方法转换:
select user_name,
replace(substring(substring_index(mobile,','a.id),char_length(substring_index(mobile,',',a.id-1))+1),',','') as mobile --截取字符串
from tb_sequence as a
cross join(
select user_name,
concat(mobile,',') as mobile, --在mobile结尾增加逗号
length(mobile)-length(replace(mobile,',',''))+1 as size --通过mobile总长度-排除逗号后的长度+1计算得出总段数
from user1 as b
) as b on a.id<=b.size
7.使用union all 将结果集 合并
SELECT user_name,'arms' as equipment, arms FROM
user1 a JOIN user1_equipment b ON a.id=b.user_id
UNION ALL
SELECT user_name,'clothing' AS equipment,clothing FROM user1 a JOIN user1_equipment b ON a.id=b.user_id
UNION ALL
SELECT user_name,'shoe' AS equipment,shoe FROM user1 a JOIN user1_equipment b ON a.id=b.user_id
最新文章
- (分享)FreeVideo1.6.1 VIP视频播放器(支持下载)
- (转)深入理解javascript连续赋值表达式
- 操作系统与c语言
- AngularJS内置指令
- [C++] C\C++ printf 输出格式
- app整体搭建环境:tabBar切换不同控制器的封装(自定义导航+自定义uiviewcontroler+系统自带tabbar+自定义tabbarController)
- JVM系列四:生产环境参数实例及分析【生产环境实例增加中】
- 如何在Visual studio中修改所使用C#语言的版本
- [置顶] Java开源代码研究总结
- R语言——绘图函数深入学习
- AutoMapper.RegExtension[.NET Core版本] 介绍
- HDU 2846 Trie查询
- CSS外边距合并问题
- ArrayBlockQueue源码解析
- js-sha256源码
- FPM五:拆解前面的四——OVP做查询和结果
- typescript 关于class属性类型定义被属性默认值覆盖的问题及解决方式
- 使用WebStorm报错 Namespace &#39;v-bind&#39; is not bound
- day24 面向对象,交互,组合,命名空间,初始继承
- Android studio Program type already present: com.****.BuildConfig