把update语句中的两个最里面的select语句拿出来,换成在@templ中加两个字段,先把他们求出来再Update select count(distinct(zydm)) from @templ where zydm<=a.zydmselect count(*) from @templ where id<=a.id and yxsm+zydm = a.yxsm + a.zydm
--假设新添加的两个字段名为aaa,bbb update a set aaa = right(1000 +(select count(distinct(zydm)) from @templ where zydm<=a.zydm),3) from @temp1 a order by idupdate a set bbb = right(10000 +(select count(*) from @templ where id<=a.id and yxsm+zydm = a.yxsm + a.zydm),4) from @temp1 a order by id --然后的update中直接使用aaa,bbb ----------------------------------------------------- 楼主试试,看效果会不会好一点!!!
有点小问题应该是:--假设新添加的两个字段名为aaa,bbb update a set aaa = right(1000 +(select count(distinct(zydm)) from sbk where zydm<=a.zydm),3) from @temp1 aupdate a set bbb = right(10000 +(select count(*) from sbk where id<=a.id and yxsm+zydm = a.yxsm + a.zydm),4) from @temp1 a --然后的update中直接使用他们
UPDATE SBK SET KSBH=B.KSBH FROM SBK AS A, (SELECT YXSM,ZYDM,BMDDM,BMH, (DWDM+'6'+RIGHT(YXSM,2)+RIGHT(1000+(SELECT COUNT(DISTINCT(ZYDM)) FROM @Templ WHERE ZYDM<=A.ZYDM),3) + RIGHT(10000+(SELECT COUNT(*) FROM @Templ WHERE ID<=A.ID AND YXSM+ZYDM = A.YXSM + A.ZYDM),4)) AS KSBH FROM @Templ A ORDER BY ID) AS B, WHERE A.BMH=B.BMH; 看了下,真的觉得很难把这个条语句优化啊。视图和索引的用处不大,因为你的临时表中用了 count(),注定了查询时用表扫描。YXSM+ZYDM = A.YXSM + A.ZYDM,另外这个条件对查询来说,也是不好处理的地方;还有就是楼主的函数了:rignt,++.等,我个人觉得还是更改你的临时表结构,看能不能尽量避免表扫描的查询方式,比如将YXSM和ZYDM和成一列。希望对楼主有帮助。 刚学不久,没多少经验。。努力中!
DECLARE LOCAL TEMPORARY TABLE @Templ(
[ID] INT IDENTITY NOT NULL ,
[DWDM] CHAR(5),
[YXSM] CHAR(3),
[ZYDM] CHAR(6),
[BMDDM] CHAR(4),
[BMH] CHAR(9));
这段都不行
2。ORDER BY ID) AS B 这里的order by没有意思,取消掉。
2。去掉了也没有明显效果,应该是几乎没有效果。
select count(distinct(zydm))
from @templ
where zydm<=a.zydmselect count(*)
from @templ
where id<=a.id
and yxsm+zydm = a.yxsm + a.zydm
update a
set aaa = right(1000 +(select count(distinct(zydm)) from @templ where zydm<=a.zydm),3)
from @temp1 a
order by idupdate a
set bbb = right(10000 +(select count(*) from @templ where id<=a.id and yxsm+zydm = a.yxsm + a.zydm),4)
from @temp1 a
order by id
--然后的update中直接使用aaa,bbb
-----------------------------------------------------
楼主试试,看效果会不会好一点!!!
update a
set aaa = right(1000 +(select count(distinct(zydm)) from sbk where zydm<=a.zydm),3)
from @temp1 aupdate a
set bbb = right(10000 +(select count(*) from sbk where id<=a.id and yxsm+zydm = a.yxsm + a.zydm),4)
from @temp1 a
--然后的update中直接使用他们
KSBH=B.KSBH
FROM SBK AS A,
(SELECT YXSM,ZYDM,BMDDM,BMH,
(DWDM+'6'+RIGHT(YXSM,2)+RIGHT(1000+(SELECT COUNT(DISTINCT(ZYDM)) FROM @Templ WHERE ZYDM<=A.ZYDM),3) +
RIGHT(10000+(SELECT COUNT(*) FROM @Templ WHERE ID<=A.ID AND YXSM+ZYDM = A.YXSM + A.ZYDM),4)) AS KSBH
FROM @Templ A
ORDER BY ID) AS B,
WHERE A.BMH=B.BMH;
看了下,真的觉得很难把这个条语句优化啊。视图和索引的用处不大,因为你的临时表中用了 count(),注定了查询时用表扫描。YXSM+ZYDM = A.YXSM + A.ZYDM,另外这个条件对查询来说,也是不好处理的地方;还有就是楼主的函数了:rignt,++.等,我个人觉得还是更改你的临时表结构,看能不能尽量避免表扫描的查询方式,比如将YXSM和ZYDM和成一列。希望对楼主有帮助。
刚学不久,没多少经验。。努力中!
再请提出好的建议,谢谢大家