这个sql有没有办法让它快点执行,数据大概不到1万条。update lsokzk_tbl lso set lso.LSZ_TEHAI_ZANSUU = (select tehai.tehaizan from tehai
where
lso.LSZ_HINCD = tehai.LSZ_HINCD
and lso.LSZ_LSKCD = tehai.LSZ_LSKCD
and lso.LSZ_LSOKCD = tehai.LSZ_LSOKCD
and lso.LSZ_KAKU = tehai.LSZ_KAKU)where exists (select 1 from tehai
where
lso.LSZ_HINCD = tehai.LSZ_HINCD
and lso.LSZ_LSKCD = tehai.LSZ_LSKCD
and lso.LSZ_LSOKCD = tehai.LSZ_LSOKCD
and lso.LSZ_KAKU = tehai.LSZ_KAKU);表tehai 其实是联合表如下,我想让大家看得简单一点没有把from tehai 写在上面。
(select sum(a.tehaizan) as tehaizan, b.LSZ_LSKCD, b.LSZ_LSOKCD, b.LSZ_HINCD, b.LSZ_KAKU
from
(select HAC_CHUUMON_NO,
HAC_HINCD,
HAC_KAKU,
HAC_LSKCD,
HAC_LSOKCD,
(HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU) as tehaizan
from hacchu_tbl
where HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU >0
union
select HAC_CHUUMON_NO,
HAC_HINCD,
HAC_KAKU,
HAC_LSKCD,
HAC_LSOKCD,
case when
(HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU)<=0 then 0 end
from hacchu_tbl
where HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU <=0) a, lsokzk_tbl b
where a.HAC_HINCD = b.LSZ_HINCD
and a.HAC_KAKU = b.LSZ_KAKU
and a.HAC_LSKCD = b.LSZ_LSKCD
and a.HAC_LSOKCD = b.LSZ_LSOKCD
group by b.LSZ_LSKCD, b.LSZ_LSOKCD, b.LSZ_HINCD, b.LSZ_KAKU
)
where
lso.LSZ_HINCD = tehai.LSZ_HINCD
and lso.LSZ_LSKCD = tehai.LSZ_LSKCD
and lso.LSZ_LSOKCD = tehai.LSZ_LSOKCD
and lso.LSZ_KAKU = tehai.LSZ_KAKU)where exists (select 1 from tehai
where
lso.LSZ_HINCD = tehai.LSZ_HINCD
and lso.LSZ_LSKCD = tehai.LSZ_LSKCD
and lso.LSZ_LSOKCD = tehai.LSZ_LSOKCD
and lso.LSZ_KAKU = tehai.LSZ_KAKU);表tehai 其实是联合表如下,我想让大家看得简单一点没有把from tehai 写在上面。
(select sum(a.tehaizan) as tehaizan, b.LSZ_LSKCD, b.LSZ_LSOKCD, b.LSZ_HINCD, b.LSZ_KAKU
from
(select HAC_CHUUMON_NO,
HAC_HINCD,
HAC_KAKU,
HAC_LSKCD,
HAC_LSOKCD,
(HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU) as tehaizan
from hacchu_tbl
where HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU >0
union
select HAC_CHUUMON_NO,
HAC_HINCD,
HAC_KAKU,
HAC_LSKCD,
HAC_LSOKCD,
case when
(HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU)<=0 then 0 end
from hacchu_tbl
where HAC_HATSU-HAC_NOUNSUU+HAC_UCHIKAN_SU <=0) a, lsokzk_tbl b
where a.HAC_HINCD = b.LSZ_HINCD
and a.HAC_KAKU = b.LSZ_KAKU
and a.HAC_LSKCD = b.LSZ_LSKCD
and a.HAC_LSOKCD = b.LSZ_LSOKCD
group by b.LSZ_LSKCD, b.LSZ_LSOKCD, b.LSZ_HINCD, b.LSZ_KAKU
)
2000条一次
limit 0,2000
first 2000
top 2000
诸如此类的函数加到select语句里面,当然后面的要做一些变化的
limit 2000,4000
select first 2000 from xxx not in (select first 2000 from xxx )
之类的