如何根据列名选择该列

[ywood]

我想在以下三个表为基础来查询数据
表A:
颜色 颜色类别
CLAB AB
CLPB PB
CLCS CS
CLWT CS
表B：
型号 零件名称 AB PB CS
4181 48衬托 15 2 5
4181 60衬托 10 0 6
4191 78衬托 2 3 5
4191 80衬托 5 8 3
表C:
型号 颜色
4181 CLAB
4191 CLWT
杳询到以下数据:
型号 颜色 零件名称 类别
4181 CLAB 48衬托 15
4181 CLAB 60衬托 10
4191 CLWT 78衬托 5
4191 CLWT 80衬托 3

[ywood]

没有人知道何解吗

[cemma]

没看动基础表的含义 :(

[Andy__Huang]

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
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.型号

[Qihua_wu]

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.型号

[lsxaa]

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 B

select 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

[xluzhong]

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.颜色

[lsxaa]

唉,笨了

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.型号

[didoleo]

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.颜色

[ywood]

但是case不能保存在view视图中,我到时在客户端如何调用这些数据呢!!