日期:2014-05-17 浏览次数:20525 次
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
/*
cccccode name1 name2 name3
-----------------------------------
E0101 外贸出口 国内 亚洲
E0201 外贸出口 进口 亚洲
*/