很多人见到文章标题以为是标题党,带着半信半疑的心态进来看一下,有些高手想顺便打击一下!
我告诉你没有机会的!因为sqltoy是中国人创建的目前最强的orm框架,为中国新基建加油!
sqltoy-orm的开源地址: https://github.com/chenrenfei/sagacity-sqltoy
我们举一个简单的例子:
select t1.STAFF_ID,t1.STAFF_NAME,t1.ORGAN_ID,
-- 子查询岗位名称,应该还有性别等,这里简化一下
(select dict_name from sys_dict_detail
where dict_type='POST_TYPE'
and dict_key=t1.POST_CODE) postName,
t2.ORGAN_NAME,
t1.DUTY_DATE,
t1.EMAIL,
t1.MOBILE_NO
from sys_staff_info t1
left join sys_organ_info t2
on t1.ORGAN_ID=t2.ORGAN_ID
where t1.name like '陈%'
and t1.DUTY_DATE>='2019-01-01'
and t1.DUTY_DATE<='2019-12-31'
order by t1.DUTY_DATE desc
很多人的分页是不管三七二十一select count(1) from (上面这段sql)
select count(1)
from sys_staff_info t1
left join sys_organ_info t2
on t1.ORGAN_ID=t2.ORGAN_ID
where t1.name like '陈%'
and t1.DUTY_DATE>='2019-01-01'
and t1.DUTY_DATE<='2019-12-31'
通过 @fast() 实现内部先分页,select * from (select xx from tablex limit ? offset ?) t1 left join tabley t2。
其结果就是:先取符合条件的数据10条再跟其他表进行关联,减少关联数据的规模从而提升效率。
select t1.*,
(select dict_name from sys_dict_detail
where dict_type='POST_TYPE'
and dict_key=t1.POST_CODE) postName,
t2.ORGAN_NAME
from @fast(select STAFF_ID,
STAFF_NAME,
ORGAN_ID,
POST_CODE,
DUTY_DATE,
EMAIL,
MOBILE_NO
from sys_staff_info
where name like '陈%'
and DUTY_DATE>='2019-01-01'
and DUTY_DATE<='2019-12-31'
order by t1.DUTY_DATE desc
) t1 left join sys_organ_info t2
on t1.ORGAN_ID=t2.ORGAN_ID
我们发现分页查询总是分成了2步:1)取count总记录;2)再取一页数据结果,总计有2次查询。我们有没有办法让2次查询变少?
sqltoy则提供了分页优化器:在一定时效内,查询条件一样利用缓存存放count数量,避免每次都2次查询
=:beginDate
and DUTY_DATE<=:endDate
order by t1.DUTY_DATE desc
) t1 left join sys_organ_info t2
on t1.ORGAN_ID=t2.ORGAN_ID
]]>
看见没有:关联数据字典表、机构表都取消了,变成了单表查询,查询出结果后通过缓存将名字翻译出来。
=:beginDate]
#[and DUTY_DATE<=:endDate]
order by t1.DUTY_DATE desc
]]>
怎么样?没有水分吧!动态条件sql是不是也很直观简洁!
页面更新:2024-04-28
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2020-2024 All Rights Reserved. Powered By 71396.com 闽ICP备11008920号-4
闽公网安备35020302034903号