a1,a2,a3三个表。 a1是一级目录 id name 1001 河北 a2是二级目录 id name 1001 石家庄 1002 保定 a3是三级目录 id name 1001 清县我一个表,是table1,一条记录有一个name字段,存储是100110011001,(就是河北保定清县) 一条记录是1001,(就是河北) 我想select name from table1查询所有记录 根据三个表得到中文的。 结果为 河北保定清县 河北这两条记录。 请问怎么做好,最好越精简越好。
select a.name,b.name+c.name+d.namefrom table1 a left join a1 b on left(a.name,4)=b.id left join a2 c on substring(a.name,5,4)=c.id left join a3 d on right(a.name,4)=d.id
修正null --------------------------------------- select a.name,isNull(b.name,'')+isNull(c.name,'')+isNull(d.name,'') from table1 a left join a1 b on left(a.name,4)=b.id left join a2 c on substring(a.name,5,4)=c.id left join a3 d on right(a.name,4)=d.id
最终版 ------------------------- select a.name,isNull(b.name,'')+isNull(c.name,'')+isNull(d.name,'') from table1 a left join a1 b on left(a.name,4)=b.id left join a2 c on case when len(a.name)>=8 then substring(a.name,5,4) else 'd' end =c.id left join a3 d on case when len(a.name)>=12 then right(a.name,4) else 'd' end=d.id
left join a1 b on left(a.name,4)=b.id
left join a2 c on substring(a.name,5,4)=c.id
left join a3 d on right(a.name,4)=d.id
---------------------------------------
select a.name,isNull(b.name,'')+isNull(c.name,'')+isNull(d.name,'')
from table1 a
left join a1 b on left(a.name,4)=b.id
left join a2 c on substring(a.name,5,4)=c.id
left join a3 d on right(a.name,4)=d.id
-------------------------
select a.name,isNull(b.name,'')+isNull(c.name,'')+isNull(d.name,'')
from table1 a
left join a1 b on left(a.name,4)=b.id
left join a2 c on
case when len(a.name)>=8 then substring(a.name,5,4) else 'd' end =c.id
left join a3 d on
case when len(a.name)>=12 then right(a.name,4) else 'd' end=d.id