select code,
type2,
(select top 1 type2 from t where code=left(a.code,4)+'00') as type1,
(select top 1 type2 from t where code=left(a.code,2)+'0000') as type
from t a
type2,
(select top 1 type2 from t where code=left(a.code,4)+'00') as type1,
(select top 1 type2 from t where code=left(a.code,2)+'0000') as type
from t a
type2,
(select top 1 type2 from t where code=left(a.code,2)+'0000') as type1,
(select top 1 type2 from t where code=left(a.code,2)+'0000') as type
from t a
type1 = (select min(type) from table1 where id = left(A.code, 4)+'00'),
type = (select min(type) from table1 where id= left(A.code, 2)+'0000')
from table1 A
--当前行为CODE=370102时,选择CODE=370100的记录的TYPE值,因为可能有多个记录,所以用MIN或TOP 1来选择一条,MAX也可以