表a
aid name mc1 mc1sl mc2 mc2sl mc3 mc3sl
1 '张三' '铅笔' '20' '毛笔' '40' '卷尺' '50'
2 '李四' '毛笔' '20' '钢笔' '40' '直尺' '50'
表b
bid name pname
1 '钢笔' '笔'
2 '毛笔' '笔'
3 '铅笔' '笔'
4 '卷尺' '尺'
5 '直尺' '尺'
输出结果
笔 尺
aid a.name 铅笔 毛笔 钢笔 直尺 卷尺
1 '张三' 20 40 0 50 0
1 '李四' 0 20 40 0 50
aid name mc1 mc1sl mc2 mc2sl mc3 mc3sl
1 '张三' '铅笔' '20' '毛笔' '40' '卷尺' '50'
2 '李四' '毛笔' '20' '钢笔' '40' '直尺' '50'
表b
bid name pname
1 '钢笔' '笔'
2 '毛笔' '笔'
3 '铅笔' '笔'
4 '卷尺' '尺'
5 '直尺' '尺'
输出结果
笔 尺
aid a.name 铅笔 毛笔 钢笔 直尺 卷尺
1 '张三' 20 40 0 50 0
1 '李四' 0 20 40 0 50
select aid,name,
case when mc1='铅笔' then mc1sl
when mc2='铅笔' then mc2sl
when mc3='铅笔' then mc3sl
else 0 end "铅笔" ,
case when mc1='毛笔' then mc1sl
when mc2='毛笔' then mc2sl
when mc3='毛笔' then mc3sl
else 0 end "毛笔" ,
case when mc1='钢笔' then mc1sl
when mc2='钢笔' then mc2sl
when mc3='钢笔' then mc3sl
else 0 end "钢笔" ,
case when mc1='卷尺' then mc1sl
when mc2='卷尺' then mc2sl
when mc3='卷尺' then mc3sl
else 0 end "卷尺" ,
case when mc1='直尺' then mc1sl
when mc2='直尺' then mc2sl
when mc3='直尺' then mc3sl
else 0 end "直尺"
from a/*
aid name 铅笔 毛笔 钢笔 卷尺 直尺
1 张三 20 40 0 50 0
2 李四 0 20 40 0 50