客户资料树型展开，要得到依次的值
有一个客户分类表：customerclass
cCCCode 客户分类编码 cCCName 类别名称 CCGrade 编码级次 bCCEnd 是否末级
当bCCEnd＝1 时为末级。
希望按末级显示，但要带上它的所有上级类别名称：
如下图所示：
create table #customerclass(cccccode varchar(10),cccname varchar(10),iccgrade int,bccend int)
insert into #customerclass
select 'E0101','亚洲','3','1' union all
select 'E01','国内','2','0' union all
select 'E','外贸出口','1','0' union all
select 'E0201','亚洲','3','1' union all
select 'E02','进口','2','0' 
希望得到如下结果：
[解决办法]
create table #customerclass(cccccode varchar(10),cccname varchar(10),iccgrade int,bccend int)
insert into #customerclass
select 'E0101','亚洲','3','1' union all
select 'E01','国内','2','0' union all
select 'E','外贸出口','1','0' union all
select 'E0201','亚洲','3','1' union all
select 'E02','进口','2','0'
;with cte1 as
(
select a.*,b.cccccode as code
from #customerclass a
inner join #customerclass b on CHARINDEX(b.cccccode,a.cccccode)>0
where b.iccgrade=1 and a.iccgrade=2
),
cte2 as
(
select a.* ,b.cccccode as code
from #customerclass a
inner join #customerclass b on CHARINDEX(b.cccccode,a.cccccode)>0
where b.iccgrade=2 and a.iccgrade=3
)
select a.cccccode,c.cccname,b.cccname,a.cccname
from cte2 a
left join cte1 b on b.cccccode=a.code
left join #customerclass c on b.code=c.cccccode
/*
E0101外贸出口国内亚洲
E0201外贸出口进口亚洲
*/
create table #customerclass(cccccode varchar(10),cccname varchar(10),iccgrade int,bccend int)
insert into #customerclass
select 'E0101','亚洲','3','1' union all
select 'E01','国内','2','0' union all
select 'E','外贸出口','1','0' union all
select 'E0201','亚洲','3','1' union all
select 'E02','进口','2','0'
select a.cccccode,c.cccname as name1,b.cccname as name2,a.cccname as name3
from #customerclass a
inner join #customerclass b on CHARINDEX(b.cccccode,a.cccccode)>0 and b.iccgrade=2 and a.iccgrade=3
inner join #customerclass c on CHARINDEX(c.cccccode,b.cccccode)>0 and c.iccgrade=1 and b.iccgrade=2
/*
cccccodename1name2name3
-----------------------------------
E0101外贸出口国内亚洲
E0201外贸出口进口亚洲
*/
数据明显是树形的，建议修改表结构：
customerclass（cCCCode，cCCName ，CCGrade，bCCEnd，Pcode[父节点编号]）
至于查询，度娘一定知道。通过Pcode来关联，性能会有明显提升。
select a.cccccode,
(select top 1 b.cccname from #customerclass b
where b.cccccode=left(a.cccccode,1) and b.iccgrade=1) 'cccname2',
(select top 1 b.cccname from #customerclass b
where b.cccccode=left(a.cccccode,3) and b.iccgrade=2) 'cccname2',
a.cccname 'cccname3'
from #customerclass a
where a.bccend=1
/*
cccccode cccname2 cccname2 cccname3
---------- ---------- ---------- ----------
E0101 外贸出口 国内 亚洲
E0201 外贸出口 进口 亚洲
(2 row(s) affected)
*/
