INSERT INTO MID_WBRATE_T select sxrq, dqdh, hbzl, llzl, ckqx, ll, llzq, bz, jlzt from ( select sxrq, dqdh, hbzl, llzl, ckqx, ll, llzq, bz, jlzt from im_rate_t where HBZL||LLZL||CKQX in (select distinct lldh from MID_LN_T where KMDH !='613101' AND KMDH != '613201' AND KMDH != '152510' AND KMDH != '152520' AND KMDH != '153901' AND KMDH != '153902') AND JLZT = '1' AND HBZL <> '01' ORDER BY sxrq DESC, dqdh DESC, hbzl, llzl, ckqx )就是这段代码
你这里面有几个影响速度的 in distinct order by 尤其是order by 你这又不显示输出,只不过插入的先后顺序而已,所以没有必要用order by
INSERT INTO mid_wbrate_t SELECT sxrq, dqdh, hbzl, llzl, ckqx, ll, llzq, bz, jlzt FROM (SELECT sxrq, dqdh, hbzl, llzl, ckqx, ll, llzq, bz, jlzt FROM im_rate_t a WHERE EXISTS ( SELECT 1 FROM mid_ln_t b WHERE b.kmdh != '613101' AND b.kmdh != '613201' AND b.kmdh != '152510' AND b.kmdh != '152520' AND b.kmdh != '153901' AND b.kmdh != '153902' AND a.hbzl || a.llzl || a.ckqx = b.lldh) AND a.jlzt = '1' AND a.hbzl <> '01')你先测试下select 出来的数据一样吗
这是用表关联的方法,其中用group by滤掉重复的 INSERT INTO mid_wbrate_t SELECT sxrq, dqdh, hbzl, llzl, ckqx, ll, llzq, bz, jlzt FROM (SELECT sxrq, dqdh, hbzl, llzl, ckqx, ll, llzq, bz, jlzt FROM im_rate_t a, (SELECT b.lldh FROM mid_ln_t b WHERE b.kmdh != '613101' AND b.kmdh != '613201' AND b.kmdh != '152510' AND b.kmdh != '152520' AND b.kmdh != '153901' AND b.kmdh != '153902' GROUP BY b.lldh) b WHERE a.hbzl || a.llzl || a.ckqx = b.lldh AND a.jlzt = '1' AND a.hbzl <> '01')你这几个语句 1、看看select出来的数据一样吗? 2、看看执行计划
或者直接两个表连接
select sxrq, dqdh, hbzl, llzl, ckqx, ll, llzq, bz, jlzt from (
select sxrq, dqdh, hbzl, llzl, ckqx, ll, llzq, bz, jlzt
from im_rate_t
where HBZL||LLZL||CKQX in
(select distinct lldh from MID_LN_T
where KMDH !='613101' AND KMDH != '613201' AND KMDH != '152510' AND KMDH != '152520' AND KMDH != '153901' AND KMDH != '153902')
AND JLZT = '1' AND HBZL <> '01'
ORDER BY sxrq DESC, dqdh DESC, hbzl, llzl, ckqx )就是这段代码
in
distinct
order by
尤其是order by 你这又不显示输出,只不过插入的先后顺序而已,所以没有必要用order by
SELECT sxrq, dqdh, hbzl, llzl, ckqx, ll, llzq, bz, jlzt
FROM (SELECT sxrq, dqdh, hbzl, llzl, ckqx, ll, llzq, bz, jlzt
FROM im_rate_t a
WHERE EXISTS (
SELECT 1
FROM mid_ln_t b
WHERE b.kmdh != '613101'
AND b.kmdh != '613201'
AND b.kmdh != '152510'
AND b.kmdh != '152520'
AND b.kmdh != '153901'
AND b.kmdh != '153902'
AND a.hbzl || a.llzl || a.ckqx = b.lldh)
AND a.jlzt = '1'
AND a.hbzl <> '01')你先测试下select 出来的数据一样吗
INSERT INTO mid_wbrate_t
SELECT sxrq, dqdh, hbzl, llzl, ckqx, ll, llzq, bz, jlzt
FROM (SELECT sxrq, dqdh, hbzl, llzl, ckqx, ll, llzq, bz, jlzt
FROM im_rate_t a,
(SELECT b.lldh
FROM mid_ln_t b
WHERE b.kmdh != '613101'
AND b.kmdh != '613201'
AND b.kmdh != '152510'
AND b.kmdh != '152520'
AND b.kmdh != '153901'
AND b.kmdh != '153902'
GROUP BY b.lldh) b
WHERE a.hbzl || a.llzl || a.ckqx = b.lldh
AND a.jlzt = '1'
AND a.hbzl <> '01')你这几个语句
1、看看select出来的数据一样吗?
2、看看执行计划