UPDATE c_temp SET Money1 = ISNULL((SELECT TOP 1 BPYLJ FROM payment_details WHERE payment_details.EmployeeID = c_temp.EmployeeID AND unitCode = '201211' AND State <> '补缴' AND State <> '利息'), 0)
WHERE execUser = 'admin' AND Item = '个人'UPDATE c_temp SET Money1 = ISNULL((SELECT TOP 1 BCYLJ FROM payment_details WHERE payment_details.EmployeeID = c_temp.EmployeeID AND unitCode = '201211' AND State <> '补缴' AND State <> '利息'), 0)
WHERE execUser = 'admin' AND Item = '公司'
因为这个类型的语句用了太多,现在几万条语句就要卡很久,求教如何优化。还有没做过索引,如何做索引?谢谢哦
WHERE execUser = 'admin' AND Item = '个人'UPDATE c_temp SET Money1 = ISNULL((SELECT TOP 1 BCYLJ FROM payment_details WHERE payment_details.EmployeeID = c_temp.EmployeeID AND unitCode = '201211' AND State <> '补缴' AND State <> '利息'), 0)
WHERE execUser = 'admin' AND Item = '公司'
因为这个类型的语句用了太多,现在几万条语句就要卡很久,求教如何优化。还有没做过索引,如何做索引?谢谢哦
SET Money1 = ISNULL(( SELECT TOP 1
BPYLJ
FROM payment_details
WHERE payment_details.EmployeeID = c_temp.EmployeeID --BPYLJ,EmployeeID,unitCode,State 建一个覆盖索引
AND unitCode = '201211'
AND State <> '补缴'
AND State <> '利息'
), 0)
WHERE execUser = 'admin' --execUser,Item,Money1 上建一个覆盖索引
AND Item = '个人'
此二表是一对多的关系, 因此,这样的优化,效率不可能高。
二者,子查询中,只有top 1无order by, 数据更新的正确性待考证。 如果只是随意对应更新,
那么左连即可。