select c.型号, c.颜色, b.零件名称, case a.颜色类别 when 'AB' then b.AB when 'PB' then b.PB when 'CS' then b.CS end as 类别 from 表A a, 表B b, 表C c where a.颜色 = b.颜色 and b.型号 = c.型号
select b.*,c.零件名称,(case when c.颜色 = 'CLAB' then b.AB else (case when c.颜色 = 'CLPB' then b.PB else (case when c.颜色 = 'CLCS' OR c.颜色='CLWT' then b.CS else end ) end ) end) from 表b b,表C c where b.型号=c.型号
select 型号,零件名称 ,'AB'+cast(isnull(AB,'') as varchar(10)) as AB ,'PB'+cast(isnull(AB,'') as varchar(10)) as PB ,'CS'+cast(isnull(AB,'') as varchar(10)) as CS into #t from Bselect a.型号,b.颜色,a.零件名称 ,case when charindex(right(b.颜色,2),a.AB)>0 then replace(a.AB,right(b.颜色,2),'') when charindex(right(b.颜色,2),a.PB)>0 then replace(a.PB,right(b.颜色,2),'') when charindex(right(b.颜色,2),a.CS)>0 then replace(a.CB,right(b.颜色,2),'') end from #t a,C b where a.型号=b.型号 where
select b.型号,c.颜色,b.零件名称,类别=isnull(a.AB,0)+isnull(a.PB,0)+isnull(a.CS,0) from 表A a,表B b,表C c where b.型号=c.型号 and c.颜色=a.颜色
唉,笨了select a.型号,b.颜色,a.零件名称 ,case when right(b.颜色,2)='AB' then a.AB when right(b.颜色,2)='PB' then a.PB when right(b.颜色,2)='CS' then a.CS end as 类别 from b a,C b where a.型号=b.型号
select b.型号,c.颜色,a.零件名称, 类别=case when a.类别='AB' then b.AB when a.类别='PB' then b.PB when a.类别='CS' then b.CS end from 表B b left join 表C c on b.型号=c.型号 left join 表A a on c.颜色=a.颜色
c.型号,
c.颜色,
b.零件名称,
case a.颜色类别
when 'AB' then b.AB
when 'PB' then b.PB
when 'CS' then b.CS
end as 类别
from
表A a,
表B b,
表C c
where
a.颜色 = b.颜色 and b.型号 = c.型号
) end
) end)
from 表b b,表C c
where b.型号=c.型号
,'AB'+cast(isnull(AB,'') as varchar(10)) as AB
,'PB'+cast(isnull(AB,'') as varchar(10)) as PB
,'CS'+cast(isnull(AB,'') as varchar(10)) as CS
into #t
from Bselect a.型号,b.颜色,a.零件名称
,case when charindex(right(b.颜色,2),a.AB)>0 then
replace(a.AB,right(b.颜色,2),'')
when charindex(right(b.颜色,2),a.PB)>0 then
replace(a.PB,right(b.颜色,2),'')
when charindex(right(b.颜色,2),a.CS)>0 then
replace(a.CB,right(b.颜色,2),'')
end
from #t a,C b
where a.型号=b.型号
where
from 表A a,表B b,表C c
where b.型号=c.型号 and c.颜色=a.颜色
,case when right(b.颜色,2)='AB' then a.AB
when right(b.颜色,2)='PB' then a.PB
when right(b.颜色,2)='CS' then a.CS
end as 类别
from b a,C b
where a.型号=b.型号
类别=case when a.类别='AB' then b.AB
when a.类别='PB' then b.PB
when a.类别='CS' then b.CS
end from 表B b left join 表C c on
b.型号=c.型号
left join 表A a on
c.颜色=a.颜色