优化SQL - MS-SQL Server / 基础类

update a set 
a.P = isnull( ( select top 1 P from B b where a.I >= b.SI and a.I <= b.EI order by (b.EI-b.SI) ),'其他') ,
a.C = isnull( ( select top 1 C from B b where a.I >= b.SI and a.I <= b.EI order by (b.EI-b.SI) ),'其他')
from A a

---

---

先更新p 再复制c

---

引用

update a set
a.P = isnull( ( select top 1 P from B b where a.I >= b.SI and a.I <= b.EI order by (b.EI-b.SI) ),'其他') ,
a.C = isnull( ( select top 1 C from B b where a.I >= b.SI and a.I <= b.EI order……

如果需求是求最小值,就没有什么好优化的了,帮顶.

---

SQL code:
WITH CTE AS(
select a.*,B.C AS BC,B.P AS BP,RN=ROW_NUMBER() OVER(PARTITION BY A.I ORDER BY b.EI-b.SI)
from A a left join B b
on a.I between b.SI and b.EI
)

UPDATE CTE
SET P=ISNULL(BP,'其他'),C=ISNULL(BC,'其他')
WHERE RN=1


????

---

---

这个不太懂，等待高人

---

高人在哪?
 数据库是 Sql2000

---

感觉最好就是这样了

---

那有没有 另外的 写法
 这个效率感觉 很低