select a.物料号,a.钢种,a.成材率,b.合格率
from(
select qbmvc1 as 物料号,substring(qbmvc1,6,4) as 钢种,qbmnc1/100 as 成材率
from mpmxva
where qbmxid='ma120'
)a join(
select qbmvc1 as 物料号,substring(qbmvc1,6,4) as 钢种,qbmnc1/100 as 成材率
from mpmxva
where qbmxid='ma122'
)b on a.物料号=b.物料号 and a.钢种=b.钢种
from(
select qbmvc1 as 物料号,substring(qbmvc1,6,4) as 钢种,qbmnc1/100 as 成材率
from mpmxva
where qbmxid='ma120'
)a join(
select qbmvc1 as 物料号,substring(qbmvc1,6,4) as 钢种,qbmnc1/100 as 成材率
from mpmxva
where qbmxid='ma122'
)b on a.物料号=b.物料号 and a.钢种=b.钢种
(case when qbmxid='ma120' then qbmnc1 end)/100 as 成材率,
(case when qbmxid='ma122' then qbmnc1 end)/100 as 成材率,
from mpmxva
where qbmxid='ma120'
union
select qbmvc1 as 物料号,substring(qbmvc1,6,4) as 钢种,0.0 成材率, qbmnc1/100 as 合格率 from mpmxva
where qbmxid='ma122'
写个case语句就能解决具体可以看联机帮助的case
when qbmxid='ma120' then qbmnc1/100 else 0.0 end,合格率 =case
when qbmxid='ma122' then qbmnc1/100 else 0.0 end
from mpmxva
zjcxc(: 邹建 :) 的是我要的结果,不过大家的我也要试试